[혼공S] CH5-(2). 제약조건으로 테이블을 견고하게

테이블을 만들 때에는 PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT 등과 같은 제약조건을 설정해줘야 한다. 이를 잘 이해하고 활용하면 데이터의 오류를 줄여 완전무결한 코드를 만들 수 있다.

 

제약조건의 기본 개념과 종류

제약조건이란 데이터의 무결성을 지키기 위해 제한하는 조건이다. 

※ 데이터의 무결성: 데이터에 결함이 없음. 

 

기본 키(PRIMARY KEY) 제약조건

데이터를 구분할 수 있는 식별자를 PRIMARY KEY, 기본 키라고 부른다. 회원 테이블의 아이디, 학생 테이블의 학번 등을 예로 들 수 있다.

• NULL값 입력 불가
• 중복값 입력 불가
• 테이블에서 기본 키는 1개만 가질 수 있음. 

CREATE TABLE에서 설정하는 기본 키 제약조건

CREATE TABLE의 쿼리문을 이용해서 PRIMARY KEY를 설정할 때에는 열 이름 뒤에 'PRIMARY KEY'를 적어주면 된다. 또는 맨 마지막 줄에 PRIMARY KEY 를 지정해주는 쿼리문을 적어줌으로써 설정 가능하다.

# PRIMARY KEY 제약조건
USE naver_db ; 
DROP TABLE IF EXISTS buy, member ; 
CREATE TABLE member (
   mem_id     CHAR(8) NOT NULL PRIMARY KEY,
   mem_name   VARCHAR(10) NOT NULL,
   height     TINYINT UNSIGNED NULL
) ; 
DESC member ;

# PRIMARY KEY 제약조건 
DROP TABLE IF EXISTS member ;
CREATE TABLE member ( 
   mem_id    CHAR(8) NOT NULL,
   mem_name  VARCHAR(10) NOT NULL,
   height    TINYINT UNSIGNED NULL,
   PRIMARY KEY (mem_id) -- 맨 마지막 줄에서 primary key 설정할 수도 있음. 
) ;

 

따라서 member 테이블에서 mem_id는 PRIMARY KEY가 되었고, 앞으로 입력되는 회원 아이디는 중복될 수 없고, 비어있을 수도 없다. 

※ member 테이블과 buy 테이블이 서로 PK-FK로 연결되어 있을 때, 외래 키(FK)가 설정된 테이블을 먼저 삭제해야 한다.

 

ALTER TABLE에서 설정하는 기본 키 제약조건

제약조건을 설정하는 또 다른 방법은 이미 만들어진 테이블을 수정하는 ALTER TABLE을 이용해서 가능하다. 먼저 기본 키가 설정되지 않은 테이블을 먼저 만들어보고, ALTER TABLE 문을 이용하여 기본키를 설정해보자.

# ALTER TABLE
DROP TABLE IF EXISTS member ;
CREATE TABLE member (
   mem_id   CHAR(8) NOT NULL,
   mem_name VARCHAR(10) NOT NULL,
   height   TINYINT UNSIGNED NULL
) ;  -- PRIMARY KEY가 지정되지 않은 경우의 테이블 생성
DESC member ;

ALTER TABLE member 
   ADD CONSTRAINT  -- 제약조건 추가
   PRIMARY KEY (mem_id) ; 
DESC member ;

기본 키에 이름을 지정할 수도 있다. 예를 들어 member 테이블의 mem_id를 PK로 지정할 때 'PK_member_mem_id'라는이름을 지정해보자. (근데 이름 지정한 거는 어디서 확인할 수 있는 거지)

# PK 이름 지정
DROP TABLE IF EXISTS member ; 
CREATE TABLE member (
   mem_id   CHAR(8) NOT NULL,
   mem_name   VARCHAR(10) NOT NULL, 
   height    TINYINT UNSIGNED, 
   CONSTRAINT PRIMARY KEY PK_member_mem_id (mem_id) -- PK 이름 지정
) ; 
DESC member ;

 

외래 키 제약조건

외래 키(FOREIGN KEY)는 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 한다. FOREIGN KEY가 설정된 열은 꼭 다른 테이블의 기본 키와 연결된다. 이때, PRIMARY KEY가 지정된 테이블을 '기준테이블'이라고 부르고, FOREIGN KEY가 지정된 테이블을 '참조테이블'이라고 부른다. 

 

CREATE TABLE에서 설정하는 외래 키 제약조건

CREATE TABLE 문을 이용하여 외래 키를 설정해보자. 기준테이블인 member 테이블을 만들고, 이후에 참조테이블인 buy를 만든다. 외래 키의 형식은 FOREIGN KEY(열_이름) REFERENCES 기준테이블(열_이름) 형식으로 이뤄지는데, 이때 구매테이블이 참조하는 회원테이블에서의 mem_id는 PRIMARY KEY로 지정된 것을 볼 수 있다.

# FOREIGN KEY 제약조건
DROP TABLE IF EXISTS member, buy ; 
-- member 테이블 만들기
CREATE TABLE member (
   mem_id   CHAR(8) NOT NULL PRIMARY KEY,
   mem_name   VARCHAR(10)  NOT NULL,
   height   TINYINT UNSIGNED
) ; 
-- buy 테이블 만들기
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)
) ;
DESC member ; 
DESC buy ;

※ 기준테이블의 PK 열 이름과 참조테이블의 FK 열 이름은 꼭 같을 필요는 없다. 

 

ALTER TABLE에서 설정하는 외래 키 제약조건

# ALTER TABLE
DROP TABLE IF EXISTS buy ; 
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) ; 
DESC buy ;

 

기준 테이블의 열이 변경될 경우

만약, 기준 테이블의 PK에서 값이 바뀌는 경우, 예를 들어 'BLK'에서 'PINK'로 바뀌었을 때를 생각해보자.

# 기준테이블의 열이 바뀔 때
DESC member ; 
INSERT INTO member VALUES('BLK', '블랙핑크', 163) ; 
DESC buy ; 
INSERT INTO buy VALUES(NULL, 'BLK', '지갑') ; 
INSERT INTO buy VALUES(NULL, 'BLK', '맥북') ; 
SELECT * FROM buy ; 

# 내부 조인을 이용하여 BLK 구매 내역 출력
SELECT M.mem_id, M.mem_name, B.prod_name
   FROM buy B 
      INNER JOIN member M 
      ON B.mem_id = M.mem_id ;

이제 UPDATE를 이용하여 member 테이블의 'BLK'를 'PINK'로 바꿔보도록 하자. 하지만 아래의 코드를 실행하면 에러가 나고 진행되지 않는다. 하지만 BLK가 구매 내역이 없다면 아래의 쿼리문은 에러없이 실행된다. DELETE 문을 써서 삭제를 해도 실행되지 않는다. 

# 'BLK' -> 'PINK' : 오류 발생
UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK' ;
# 'BLK' 삭제 : 오류 발생
DELETE FROM member WHERE mem_id = 'BLK' ;

기준테이블의 PK 값이 바뀌면 참조 테이블의 FK 값도 자동으로 바뀌게 하는 기능이 'ON UPDATE CASCADE'와 'ON DELETE CASCADE' 문이다. 즉, 기준 테이블의 데이터가 UPDATE/DELETE되면 참조 테이블의 데이터도 UPDATE/DELETE가 되는 것이다. 

# ON UPDATE/DELETE CASCADE
DROP TABLE IF EXISTS buy ; 
CREATE TABLE buy (
   num    INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   mem_id   CHAR(8) NOT NULL, 
   prod_name   CHAR(6) NOT NULL
) ; 
DESC buy ; 
ALTER TABLE buy 
   ADD CONSTRAINT 
   FOREIGN KEY(mem_id) REFERENCES member(mem_id) 
   ON UPDATE CASCADE 
   ON DELETE CASCADE ; 

INSERT INTO buy VALUES(NULL, 'BLK', '지갑') ; 
INSERT INTO buy VALUES(NULL, 'BLK', '맥북') ;
SELECT * FROM buy ; 

UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK' ;  
SELECT * FROM member ; 
SELECT * FROM buy ;

실행을 하면 에러 발생 없이 잘 진행되는 걸 볼 수 있다. 

내부조인을 이용하여 아까 출력했던 'PINK'의 구매내역을 출력해보자.

SELECT M.mem_id, M.mem_name, B.prod_name
   FROM buy B
      INNER JOIN member M
      ON B.mem_id = M.mem_id ;

DELETE 문도 정상적으로 실행이 된다.

DELETE FROM member WHERE mem_id = 'PINK' ; 
SELECT * FROM member ; 
SELECT * FROM buy ;

 

기타 제약조건

고유 키(UNIQUE) 제약조건

UNIQUE는 '중복되지 않는 유일한 값'을 입력해야 하는 조건이다. PRIMARY KEY와 비슷하지만 UNIQUE는 NULL값을 허용한다. 이때 NULL값은 여러 개가 입력되어도 상관없다. 또한 UNIQUE는 한 테이블에 여러 열이 설정해도 된다. 

# UNIQUE
DROP TABLE IF EXISTS buy, member ; 
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 
) ; 
INSERT INTO member VALUES('BLK', '블랙핑크', 163, 'pink@gmail.com') ;
INSERT INTO member VALUES('TWC', '트와이스', 167, NULL) ;
INSERT INTO member VALUES('APN', '에이핑크', 164, 'pink@gmail.com') ;  -- 에러발생: 중복된 값

 

체크 제약조건

체크(CHECK)는 입력되는 데이터를 점검하는 기능을 하는 제약조건이다. 두 번째 INSERT 문에서 CHECK 조건이 만족되지 않아 실행되지 않는 것을 볼 수 있다.

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 CHECK(height >= 100), 
   phone1 CHAR(3) NULL
) ; 
DESC member ; 
INSERT INTO member VALUES('BLK', '블랙핑크', 163, NULL) ; 
INSERT INTO member VALUES('TWC', '트와이스', 67, NULL) ; -- 에러발생

ALTER TABLE member
   ADD CONSTRAINT 
   CHECK ( phone1 IN ('02', '031', '032', '054', '055', '061') ) ; -- 체크 조건 추가

INSERT INTO member VALUES('TWC', '트와이스', 167, '02') ; 
INSERT INTO member VALUES('OMY', '오마이걸', 167, '010') ; -- 에러 발생

 

기본값 정의

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

# DEFAULT 값 정의
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 DEFAULT 160, 
   phone1  CHAR(3) NULL
) ; 
ALTER TABLE member 
   ALTER COLUMN phone1 SET DEFAULT '02' ; -- 열의 조건을 바꿀 때에는 ALTER COLUMN

INSERT INTO member VALUES('RED', '레드벨벳', 161, '054') ; 
INSERT INTO member VALUES('SPC', '우주소녀', DEFAULT, DEFAULT) ; 
SELECT  * FROM member ;

 

Reference

혼자 공부하는 SQL, 우재남 지음