[MySQL] 10. SQL 테이블 제약조건(기본키, 외래키, 고유키)

제약조건은 데이터의 무결성을 지키기 위해 제한하는 조건이다. 여기서 데이터의 무결성이란 '데이터에 결함이 없음'을 의미한다. 간단한 예로 네이버 회원의 아이디가 중복되면 이메일, 블로그, 쇼핑 기록 등 상당한 혼란이 일어날 것이다. 이러한 것이 바로 데이터의 결함이고, 이런 결함이 없는 것을 데이터의 무결성이라고 표현한다.

 

MySQL에서 제공하는 대표적인 제약조건으로는 6가지가 존재한다.

PRIMARY KEY 제약조건, FOREIGN KEY 제약조건, UNIQUE 제약조건, CHECK 제약조건, DEFAULT 정의, NULL 값 허용

---

테이블에는 많은 행 데이터가 존재한다. 이 중에서 데이터를 구분할 수 있는 식별자를 기본 키(Primary Key)라고 부른다.

 

기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없다. 또한 기본 키는 한 테이블당 한 개만 지정할 수 있다.

 

DESCRIBE 명령어를 통해 테이블의 구성을 살펴볼 수 있다.

USE naver_db;
DROP TABLE IF EXISTS member;

CREATE TABLE member (
    mem_id CHAR(8) NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10) NOT NULL,
    height TINYINT UNSIGNED NULL
);

DESCRIBE member;

 

Result

 

 

기본키를 지정하는 또 다른 방법

CREATE TABLE member (
    mem_id CHAR(8) NOT NULL,
    mem_name VARCHAR(10) NOT NULL,
    height TINYINT UNSIGNED NULL,
    PRIMARY KEY (mem_id)
);

 

CREATE TABLE member (
    mem_id CHAR(8) NOT NULL,
    mem_name VARCHAR(10) NOT NULL,
    height TINYINT UNSIGNED NULL
);

ALTER TABLE member
    ADD CONSTRAINT
    PRIMARY KEY (mem_id);

---

외래(Foreign) 키 제약조건은 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 한다.

외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결되어야 한다.

 

CREATE TABLE buy (
    num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    mem_id CHAR(8) NOT NULL,
    prod_name CHAR(6) NOT NULL,
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

DESCRIBE buy;

 

 

Result

 

 

외래 키를 설정하는 또 다른 방법

CREATE TABLE buy (
    num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    mem_id CHAR(8) NOT NULL,
    prod_name CHAR(6) NOT NULL
);

ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id) REFERENCES member(mem_id);

 

외래 키를 설정한 경우 기준 테이블의 기본 키 값을 수정하거나 삭제하려고 할 경우 오류가 발생한다.

하지만 만약 값을 변경하고 싶다면 ON UPDATE CASCADE문과 ON DELETE CASCADE문을 사용할 수 있다.

 

ON UPDATE CASCADE문은 기준 테이블의 데이터가 변경될 시 자동으로 참조 테이블의 값도 변경되는 기능을 지원하며

ON DELETE CASCADE 문은 기준 테이블의 데이터가 삭제될 시 참조 테이블의 데이터도 삭제되는 기능을 지원한다.

 

CREATE TABLE buy (
    num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    mem_id CHAR(8) NOT NULL,
    prod_name CHAR(6) NOT NULL
);

ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

 

이후 데이터를 수정 또는 삭제할 경우 오류가 발생하지 않는다.

---

고유(Unique) 키 제약조건은 '중복되지 않는 유일한 값'을 입력해야 하는 조건이다. 이것은 기본 키 제약조건과 거의 비슷하지만, 차이점은 고유 키 제약조건은 NULL 값을 허용한다는 것이다.

NULL 값은 여러 개가 입력되어도 상관없으며, 기본 키는 테이블 당 1개만 설정할 수 있지만 고유 키는 여러 개 설정해도 상관없다.

 

CREATE TABLE member (
    mem_id CHAR(8) NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10) NOT NULL,
    height TINYINT UNSIGNED NULL,
    email CHAR(30) NULL UNIQUE
);

---

체크(Check) 제약조건은 입력되는 데이터를 점검하는 기능을 한다. 예를 들어 평균 키(height)에 마이너스 값이 입력되지 않도록 하거나, 연락처의 국번에 02, 031, 041, 055 중 하나만 입력되도록 할 수 있다.

 

CREATE TABLE member (
    mem_id CHAR(8) NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10) NOT NULL,
    height TINYINT UNSIGNED NULL CHECK(height >= 100),
    phone1 CHAR(3) NULL
);

ALTER TABLE member
    ADD CONSTRAINT
    CHECK (phone1 IN ('02', '031', '032', '054', '055', '061'));

---

기본값(Default) 정의는 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.

 

ALTER TABLE 사용 시 열에 DEFAULT를 지정하기 위해서는 ALTER COLUMN 문을 사용한다.

CREATE TABLE member (
    mem_id CHAR(8) NOT NULL PRIMARY KEY,
    mem_name VARCHAR(10) NOT NULL,
    height TINYINT UNSIGNED NULL DEFAULT 160,
    phone1 CHAR(3) NULL
);

ALTER TABLE member
    ALTER COLUMN phone1 SET DEFAULT '02';
    
INSERT INTO member VALUES('RED', '레드벨벳', 161, '054');
INSERT INTO member VALUES('SPC', '우주소녀', default, default);