DB : 기본키, 외래키, 제약조건

PRIMARY KEY(기본키)

튜플을 유일하게 식별하기 위해 사용되는 속성들의 부분집합(후보키) 중 선정된 key이다.

즉 테이블 튜플(행)들의 식별자이다.

기본키 설정시 NOT NULL과 UNIQUE 제약조건을 갖는다.

NOT NULL을 기본적으로 갖지만 명시적으로 알려주기 위해 기본키 선언시 NOT NULL 붙여주기

FOREIGN KEY(외래키)

다른 테이블의 기본키를 참조하는 속성 또는 속성들의 집합이다.

+ 다른 테이블의 UNIQUE 제약조건을 갖는 속성 외래키로 참조가 가능하다.

 

A가 B를 참조한다고할때, A는 자식테이블, B는 부모테이블이다.

테이블간 연관관계를 설정하며 무결성을 보장한다.

DEFAULT NULL 설정하기

 

외래키 vs 인덱스

외래키는 참조시 무결성을 보장하지만 데이터 추가/삭제시 제약사항이 있다.

그러므로 실무에서는 주로 인덱스를 사용한다고 한다.

 

TradeOff

외래키를 사용하면 무결성을 보장할 수 있지만, 오버헤드 및 제약사항이 존재하고,

인덱스를 사용하면 제약사항은 없고 빠르게 접근이 가능하지만, 무결성을 보장할 방법을 마련해야한다.

 

무결성이란?

정보통신용어 사전에서 정의한 무결성은 신뢰할 수 있는 서비스 제공을 위해서 의도하지 않은 요인에 의해 데이터가 변경되거나 손상되지 않고 완전성, 정확성, 일관성을 유지함을 보장하는 특성이다.

즉 테이블에 존재하는 데이터들이 올바른 데이터를 가지고 있음을 뜻한다.

예시

학생 테이블의 학생 전공을 성적 테이블과 기숙 테이블에서 사용하고 있다고 하자.

 

전과 면접이 시행되고 결과가 발표된 뒤 약 100명의 학생의 전공이 바뀌었다면,

해당 학생에 대한 학생 테이블, 성적 테이블, 기숙 테이블의 전공값을 모두 변경해야할 것이다.

만약 변경 내용이 모두 수정되지 않았을 경우 여러 문제가 발생하고, 변경사항이 완전히 반영되었는지 확인하기 어렵다.

 

이때 외래키를 사용하면 편리하게 무결성을 보장할 수 있다.

학생 테이블의 학생 전공을 성적 테이블과 기숙 테이블에서 외래키로 참조한다면, 학생 테이블이 부모 테이블, 성적 테이블과 기숙 테이블이 자식 테이블이 된다.

 

학생 전공이 바뀌었을때 외래키로 참조하는 테이블은 미리 설정된 옵션에 맞게 동작하게 된다.

CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION(기본값)

 

만약 CASCADE 옵션을 사용해 UPDATE를 설정해 주었다면, 

학생 전공 변경시 성적 테이블과 기숙 테이블에서 참조하는 학생 전공이 변경된 값으로 갱신되어 올바른 값(무결성 보장)으로 참조할 수 있게 된다.

 

ON DELETE/UPDATE

CASCADE

부모 테이블 데이터 변경/삭제시 자식 테이블의 데이터도 함께 변경/삭제

SET NULL

부모 테이블 데이터 변경/삭제 시 자식 테이블의 참조 값을 NULL로 설정

SET DEFAULT

부모 테이블의 데이터 변경/삭제시 자식 테이블의 참조 컬럼을 Default 값으로 설정

RESTRICT

자식 테이블이 참조하고 있을 경우, 부모 테이블의 데이터 변경/삭제 작업 취소

(ON UPDATE시 변경 불가, ON DELETE시 삭제 불가)

NO ACTION

ON DELETE 제약조건을 사용하지 않았을 때 기본으로 설정되며, MySQL에서 RESTRICT와 같다.

 

✅ 부모에 제약조건을 적용하므로 부모 갱신/삭제 기준이다.

 

UNIQUE

중복되지 않는 유일한 값을 입력해야한다. PK와 달리 NULL값을 허용한다.

 

제약 조건 적용 예제

CREATE TABLE item (
    id INT(11) NOT NULL AUTO_INCREMENT,
    order_id INT(11) NOT NULL,
    option_id INT(11) NOT NULL,
    quantity INT(11) NOT NULL,
    price INT(11) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (order_id) REFERENCES order(id)
    ON DELETE CASCADE,
    FOREIGN KEY (option_id) REFERENCES option(id)
    ON DELETE SET NULL,
);

• 주문(order) 자체가 삭제될 경우, item도 함께 삭제되도록 ON DELETE CASCADE를 사용했다.
• 상품 옵션이 삭제되더라도 주문 데이터를 유지하기 위해 ON DELETE SET NULL을 사용했다.

 

상황에 따라 필요한 제약조건

CASCADE

✅ ON UPDATE CASCADE

부모 테이블의 값이 변경될때 자식 테이블에서 변경된 값을 가져와야할 경우 필요하다.

❕ 만약 참조값이 변경되지 않는 값이라면(ex) id) 굳이 ON UPDATE CASCADE를 사용할 필요는 없다.

 

✅ ON DELETE CASCADE

부모 테이블의 값(ex)유저 id)이 삭제될 경우 자식 테이블의 값이 함께 삭제되어야한다면 필요하다.

 

RESTRICT

참조하는 값이 변경/삭제되지 않아야할 경우 RESTRICT 옵션을 사용할 수 있다.

🤔 자식 테이블의 참조로 인해 부모 테이블의 변경/삭제 작업이 제한된다.

 

SET NULL

✅ ON DELETE SET NULL

부모 테이블의 값이 삭제 되었을때 자식 테이블의 참조값은 NULL값으로 설정된다.

즉, 자식 테이블의 값이 삭제되는 것을 막아 데이터 유실을 방지할 수 있다.