[TIL / SQL] relation, type, constraint, FK (MySQL)

이 글은 아래 강의를 바탕으로 작성했습니다.

[지금 무료] 시니어 백엔드 개발자가 알려주는 데이터베이스 개론 & SQL | 쉬운코드 - 인프런

 

 

relation in SQL

Relational Data Model의 relation은 SQL에서 table을 의미한다(완전히 구분 짓지는 않음). 그리고 SQL에서 relation이란 `multiset of tuples`로 중복된 tuple을 허용한다는 의미이다.

`multiset`은 중복을 하용하지 않는 `set`과 달리 중복을 허용하므로 각 tuple이 중복될 수 있다.

 

 

Type in SQL(MySQL)

char vs varchar

MySQL에서는 휴대전화 번호와 같이 길이가 일정한 경우에는 `char`, 그렇지 않은 경우에는 `varchar`를 쓰는 것이 좋다. `char`가 `varchar`보다 속도가 빠르기 때문에 무조건 `varchar`를 쓰는 것보다 위와 같은 경우를 따져보고 선택하자.

 

byte-string

해당 타입은 문자열이 아닌 byte string을 저장하는데 쓰인다. MySQL에는 `BINARY`, `VARBINARY`, `BLOB` 타입이 해당된다. 이는 암호화할 때의 키를 저장할 때 쓰인다. 

 

 

Constraint

UNIQUE

`unique`는 중복을 허용하지 않는다.

 

• `NULL` 중복 허용은 DB마다 다르다. MySQL은 `NULL` 중복을 허용한다. 
• 보통 `NULL`을 허용하지 않는 `NOT NULL`과 같이 사용한다.

 

CHECK

• attribute의 값을 제한하고 싶을 때 사용한다.
• Ex. CHECK (age > 20)  →  20살 이하는 저장하지 않는다.
• 둘 이상의 attribute로 구성될 때는 attribute의 바로 옆이 아닌, 맨 아래에 기입한다. 

 

constraint 이름 명시하기

• 이름을 붙이면 어떤 constraint를 위반했는지 쉽게 파악할 수 있다.
• constraint를 삭제하고 싶을 때 해당 이름으로 삭제할 수 있다.

Example SQL

create table TEST (
	age INT CONSTRAINT age_over_20 CHECK(age>20)
);

• CHECK(age>20): 나이가 20살이 넘도록 constraint를 걸어 놓음
  • 이에 대해 이름 붙이기: `CONSTRAINT {constraint 이름}`
• 이름을 붙인 상태로 constraint를 위반하면 다음과 같이 에러가 난다   →  `Check constraint 'age_over_20' is violated.`

 

 

FOREIGN KEY 선언 방법

MySQL

create table EMPLOYEE ( 
	... 
	dept_id INT, FOREIGN KEY (dept_id) references DEPARTMENT(id) 
            on delete reference_option 
            on update reference_option 
);

Notice!  참고하고 있는 값이 삭제되거나 변경될 때 FK의 값은 어떻게 할 것인지 지정해야 함

              → `on delete reference_option`

              → `on update reference_option`

 

 

옵션

• CASCADE: 참조값의 삭제/변경을 그대로 반영
• SET NULL: 참조값이 삭제/변경 시 `NULL`로 변경
• RESTRICT: 참조값이 삭제/변경되는 것을 금지
• NO ACTION: `RESTRICT`와 유사
  • MySQL은 `NO ACTION`과 `RESTRICT` 완전히 동일
  • 표준 SQL: 한 트랜잭션 내의 여러 개의 SQL이 실행되는 동안에는 참조값이 삭제/변경되는 것을 허용
• SET DEFAULT: 참조값이 삭제/변경 시 `default` 값으로 변경
  • MySQL은 제대로 지원하지 않음.

   → MySQL에서 지원하는 reference_option은 `CASCADE`, `SET NULL`, `RESTRICT`

   → PostgreSQL은 5가지 모두 지원

 

 

ALTER

테이블이 생성된(create) 후에 테이블의 스키마를 변경할 때는 `ALTER TABLE` 문을 사용한다.

 

FK 옵션을 추가하는 경우의 예시

ALTER TABLE {테이블 명} ADD FOREIGN KEY (FK를 설정할 컬럼) REFERENCES {참조할 테이블}(참조할 테이블의 참조할 컬럼명)
    on update CASCADE
    on delete SET NULL;
    
# Example
ALTER TABLE department ADD FOREIGN KEY (leader_id) REFERENCES employee(id)
    on update CASCADE
    on delete SET NULL;

• `department` 테이블의 `leader_id`에 `FK`를 설정한다.
• `leader_id`는 `employee` 테이블의 `id` 컬럼을 참조한다.
• 나머지는 변경/삭제 시의 설정