[SQL] 제약조건, 키(Key), 인덱스

1. 테이블 만들때 제약조건 설정하기

-- borrow 테이블을 정의하세요.
CREATE TABLE borrow(
    customer_number VARCHAR(10),
    rental_time DATETIME,
    rental_status ENUM('대여', '반납') NOT NULL,
    rental_location VARCHAR(20) NOT NULL,
    kickboard_id VARCHAR(10),
    CONSTRAINT borrow_pk PRIMARY KEY(customer_number,rental_time), # 기본키 2개
    FOREIGN KEY (customer_number) REFERENCES customer (customer_number), customer테입블의 customer_number 열을 외래키로 추가
    FOREIGN KEY (kickboard_id) REFERENCES kickboard (id)   
);

 

2. 이미 만들어진 테이블에 제약조건 추가하기

- ALTER TABLE 테이블명 ADD CONSTRAINT 커스텀제약조건명 제약조건명 (컬럼명 또는 제약조건)

ALTER TABLE customer
  ADD CONSTRAINT pk_customer PRIMARY KEY (id),
  ADD CONSTRAINT uq_email UNIQUE (email),
  ADD CONSTRAINT fk_region FOREIGN KEY (region_id) REFERENCES region(id),
  ADD CONSTRAINT ck_age CHECK (age >= 19);
  
  -- mysql은 커스텀 이름 없이도 가능
ALTER TABLE customer ADD UNIQUE (email);
ALTER TABLE customer ADD CHECK (age >= 19);

 

1) ADD CONSTRAINT로 추가 가능한 제약조건 종류

제약조건	설명	예시
PRIMARY KEY	기본키(중복x, NULL x)	ADD CONSTRAINT pk PRIMARY KEY(id)
UNIQUE	유일 값(중복x)	ADD CONSTRAINT uq_email UNIQUE(email)
FOREIGN KEY	외래 키(다른 테이블 참조)	ADD CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id)
CHECK	조건 검사(MySQL 8.0이상)	ADD CONSTRAINT ck_age CHEK (age>=0)
DEFAULT	기본값 설정 (X add constraint로는 안됨)	ALTER TABLE user ALTER COLUMN age SET DEFAULT 18

* DEFAULT는 ADD CONSTRAINT대신 ALTER TABLE users ALTER COLUMN age SET DEFAULT 18 별도 구문 사용

** UNIQUE시 NULL 값은 비교가 불가능하여 중복되어도 에러가 발생하지 않는다.

 

2. Unique

- 중복되는 값을 비허용, 똑같은 값이 있으면 에러가 발생함, Null 값은 비교가 불가능해 중복되어도 에러가 발생하지 않음

CREATE TABLE customer(
	id VARCHAR(10) UNIQUE,
    name VARCHAR(10) NOT NULL
);

INSERT INTO customer(id, name) VALUES(1,'홍길동');
INSERT INTO customer(id, name) VALUES(1,'김철수'); # ERROR

INSERT INTO customer(name) VALUES('김철수');

 

3. Default

- 기본 값을 설정, 아무런 값을 지정하지 않으면 default 값으로 설정됨

ALTER TABLE customer MODIFY COLUMN address VARCHAR(30) DEFAULT 'No Address';
INSERT INTO customer(name) VALUES('김나나');
SELECT * FROM customer;
DESC customer;

 

4. CHECK(mysql 8.0 이상부터 작동)

- 값의 범위를 제한하여 특정 값만 허용, 제한한 값이 아닌 경우 에러를 발생한다

ALTER TABLE customer ADD COLUMN age INT CHECK (age>=19);

insert into customer(name, age) values('송사리', 9)# 에러
insert into customer(name, age) values('송사리', 20)

 

5. Constraint

- 제약 조건 이름 및 제약조건 정의

DROP TABLE customer;
DESC customer;

CREATE TABLE customer(
	id VARCHAR(10),
    age INT,
    CONSTRAINT id_unique UNIQUE(id),
    CONSTRAINT age_check CHEKCK(age >= 10)
);

-- 생성된 제약 조건 확인
SELECT * FROM information_schema.table_constraints;

-- 제약조건 추가하기(alter table 테이블명 add constraint 제약조건이름 제약조건(속성))
ALTER TABLE customer ADD COLUMN address varchar(30);
ALTER TABLE customer ADD CONSTRAINT address_unique UNIQUE (address);
ALTER TABLE customer ADD CONSTRAINT customer_chk_2 CHECK (age >= 19 AND name='송사리');

-- 제약조건 수정하기(alter table 테이블명 alter 속성 set default 기본값)
ALTER TABLE customer ALTER address SET DEFAULT '주소 없음';

DESC customer;

-- 제약조건 삭제하기(alter table customer drop constraint 제약조건 이름)
ALTER TABLE customer DROP CONTRAINT customer_chk_2;
ALTER TABLE customer ALTER address DROP DEFAULT;

-- 제약조건 중 check을 직접 수정하는건 안되고, 삭제 후 재생성만 가능!(postgreSQL은 가능)

 

6. 데이터 모델과 구성 요소

- 개체(Entity): 데이터로 표현하고자 하는 현실 세계의 개념이나 정보의 단위(ex. 킥보드, 회원)

- 속성(Attribute): 개체에 대한 정보(ex. 킥보드의 브랜드/이용 가격, 회원의 아이디/이름)

- 관계(Relationship): 개체 간의 연관성(ex. 고객은 킥보드를 대여한다)

 

7. 데이터 모델링

- 개념적 설계: 현실 세계를 추상적 개념으로 표현

- 논리적 설계: DBMS가 처리할 수 있는 데이터 구조(스키마) 설계

- 물리적 설계: DBMS에 테이블을 저장할 구조를 설계

 

8. ER 다이어그램(ERD)

- 현실 세계의 데이터를 개체와 관계 형태의 다이어그램으로 나타내는 것

- 그려보기 : https://app.diagrams.net/

Flowchart Maker & Online Diagram Software

 

1) Peter Chen

기호	의미
	개체
	속성
	관계
	개체와 속성의 연결
	기본키

 

2) 요구사항

-예: 고객은 회원 가입 시 이름, ID, PW, 전화번호, 생년월일을 입력한다

 

3) 관계

- 하나의 데이터가 다른 테이블의 몇 개의 데이터와 관계를 가지는지 나타냄

- 1:1 관계

- 1:N 관계

- N:M 관계

 

9. ER 다이어그램(IE - Information Engineering)

- 키를 나타내며 데이터 타입과 제약 조건도 표기

- 관계를 세부적으로 나타냄

 

1) 까마귀발 표기법

기호	의미
	0 또는 1
	정확히 1
	0 이상
	1이상

 

10. 키

- 기본키: 서로 다른 튜플을 유일하게 식별할 수 있는 속성으로 널 값을 가질 수 없고, 테이블 당 1개만 설정 가능하다

- 외래키: 

- 후보키: 기본키가 될 수 있는 키로, 유일성과 최소성을 만족한다

- 대체키: 기본키가 될 수 있는 키로 유일성과 최소성을 만족한다

- 슈퍼키: 튜플을 식별할 수 있는 키로 유일성을 만족한다.

* 유일성: 각 튜플을 서로 구분할 수 있어야 한다(키 값이 중복되면 안된다).

** 최소성: 키를 구성하는 속성 중 불필요한 속성이 없어야 한다.(키를 이루는 속성에서 하나라도 빼면 유일성이 깨져야 함)

 

11. 인덱스

- CREATE INDEX 커스텀인덱스명ON 테이블명 (컬럼명)