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

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

---

테이블을 만들 때는 테이블의 구조에 필요한 제약조건을 설정해줘야 한다.

기본 키(Primary Key)는 학번, 아이디, 사번 등과 같은 고유한 번호를 의미하는 column에 지정한다.

외래 키(Foreign Key)는 기본키와 연결되는 column에 지정한다.

이메일, 휴대폰 번호와 같이 중복되지 않는 열에는 고유 키(Unique)를 지정할 수 있다.

회원의 평균 키를 넣는다고 가정할 때, 당연히 평균 키는 2m를 넘지 않을 것이다. 이때 실수로 200cm을 입력하는 것을 방지하는 제약 조건이 체크(Check)이다.

국내에서 서비스하는 프로그램을 만든다고 하면, 회원 테이블에 국적은 대부분이 대한민국일 것이다. 이러한 경우에는 국적이 대한민국으로 기본값(Default)을 설정할 수 있다.

또한, 값을 꼭 입력해야 한다면 NOT NULL 제약 조건을 설정할 수도 있다.

 

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

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

데이터의 무결성이란 데이터에 결함이 없음을 의미한다.

MySQL에서 제공하는 대표적인 제약조건은아래와 같다.

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

 

기본키 제약조건

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

기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없다.

대부분의 테이블은 기본 키를 가져야 한다. 물론, 기본 키가 없어도 테이블 구성이 가능하지만 실무에서 사용하는 테이블에는 기본 키를 설정해야 중복된 데이터가 입력되지 않는다.

또한 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다.

마지막으로 하나의 테이블에서는 기본 키를 1개만 가질 수 있다.

어떠한 열에 기본키를 설정해도 문법상 문제는 없으나 테이블의 특성을 가장 잘 반영하는 열을 선택해야 한다.

 

기본키 지정하는 방법

기본키를 지정하는 방법은 세 가지가 있다.

• 첫 번째 방법

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

• 두 번째 방법

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);

기본키에 이름 지정하기
기본 키는 별도의 이름이 없으며, DESCRIBE 명령으로 확인하면 그냥 PRI로만 나온다.
필요하다면 기본 키의 이름을 직접 지어줄 수 있다.
예를 들어, PK_member_mem_id와 같은 이름을 붙여주면, 이름 만으로도 'PK가 member 테이블의 mem_id열에 지정됨'이라고 이해할 수 있다.

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

 

외래 키 제약조건

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

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

회원 테이블과 구매 테이블이 바로 대표적인 기본 키- 외래 키 관계이다.

여기서 기본 키가 있는 회원 테이블을 기준 테이블이라고 부르며, 외래 키가 있는 구매 테이블을 참조 테이블이라고 부른다.

구매 테이블의 아이디(FK)는 반드시 회원 테이블의 아이디(PK)로 존재한다.

쇼핑몰 데이터베이스에 제품을 구매한 기록이 있는 사람은 쇼핑몰 회원이라는 의미이다. 그러므로 구매한 기록은 있으나 구매한 사람이 누군지 모르는 심각한 일은 절대 발생하지 않는다.

구매 테이블의 데이터는 모두 누가 구매했는지 확실히 알 수 있는, 무결한 데이터가 되는 것이다.

또 하나 기억해야할 것은 참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나, 고유 키로 설정되어 있어야 한다.

테이블을 삭제하는 순서
회원 테이블과 구매 테이블은 기본 키-외래 키로 연결되어 있다.
만약, 구매 테이블이 있는데 회원 테이블을 삭제하면 이는 무결성 제약조건에 위배된다.
예를 들어, 구매 테이블에 있는 특정한 회원의 정보를 알고 싶어도 회원 테이블이 삭제되었기 때문에 알 수 있는 방법이 없다. 이러한 경우는 무결한 데이터라고 할 수 없다.
따라서 기본 키-외래 키 관계로 연결된 테이블은 외래 키가 설정된 테이블을 먼저 삭제해야 한다.

회원이 아닌데 구매 테이블을 구매할 수 있는가?
회원 테이블과 구매 테이블은 PK-FK 관계이다.
즉, 회원이 아닌데 구매 테이블에 회원이 입력될 수 없다.
이는 외래키 제약조건에 위반되기 때문이다.

 

외래키 지정하는 방법

외래키를 설정하는 방법은 두 가지가 있다.

외래 키의 형식은 FORIGN KEY(열_이름) REFERENCES 기준_테이블(열_이름) 이다.

• 첫 번째 방법

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)
);

• 두 번째 방법(테이블 생성 후, 제약조건 추가)

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);

 

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

만약, 회원 테이블의 BLK가 물품을 2건 구매한 상태에서 회원 아이디를 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;

내부 조인을 사용해서 물품 정보 및 사용자 정보를 확인해 보면, 결과가 정상적으로 나왔다.

하지만, BLK의 아이디를 PINK로 변경하면 아래처럼 오류가 발생한다.

PK-FK로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않는다.

열 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문이다.

지금은 회원 테이블의 BLK가 물건을 구매한 기록이 존재하기 때문에 변경할 수 없는 것이다.
만약, BLK가 구매한 적이 없다면(구매 테이블에 데이터가 없다면) 회원 테이블의 BLK는 변경 가능하다.

삭제를 시도해 보아도, 같은 오류로 삭제되지 않는다.

 

기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경될 수 있도록 하는 방법이 있다.

ON UPDATE CASCADE문을 사용하면 된다.

또한 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제될 수 있도록 하는 방법도 존재한다.

ON DELETE CASCADE문을 사용하면 된다.

buy 테이블을 삭제하고 다시 만든 뒤, 아래의 제약조건을 추가해 준다.

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

 

이제 회원 테이블의 BLK를 PINK로 변경하면 오류 없이 잘 변경이 된다.

다시 내부 조인을 사용해서 물품 정보 및 사용자 정보를 확인해 보면, 기준 테이블과 참조 테이블의 아이디가 모두 변경된 것을 확인할 수 있다.

 

PINK가 탈퇴한 것으로 가정하고 기준 테이블에서 삭제해도 오류 없이 잘 실행되는 것을 확인할 수 있다.

구매 테이블을 확인하면 아무것도 없다.

 

 

기타 제약조건

고유 키 제약조건

고유 키 제약조건은 '중복되지 않는 유일한 값'을 입력해야 하는 조건이다.

기본 키 제약조건과 거의 비슷하지만, 차이점은 고유 키 제약조건은 NULL값을 허용한다는 점이다.

또한 기본 키는 테이블에 1개만 설정해야 하지만, 고유 키는 여러 개를 설정해도 된다.

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');

같은 이메일을 중복으로 삽입하면 아래와 같이 오류를 내뿜는다.

 

체크 제약조건

체크 제약조건은 입력되는 데이터를 점검하는 기능을 한다.

예를 들어 평균 키에 마이너스 값이 입력되지 않도록 하거나, 연락처의 국번에 02, 031, 041, 055 중 하나만 입력되도록 할 수 있다.

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
);

이렇게 height에 check 제약조건으로 키가 100 이상이 되도록 설정한 후

INSERT INTO member VALUES('BLK', '블랙핑크', 163, NULL);
INSERT INTO member VALUES('TWC', '트와이스', 99, NULL);

키가 100 미만이면 아래와 같은 오류를 내뿜는다.

이유는 체크 제약조건에 위배되었기 때문이다.

 

필요하다면 테이블을 만든 후에 ALTER TABLE 문으로 제약 조건을 추가해도 된다.

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

이렇게 제약조건을 설정하면 02, 031, 032, 054, 055, 061 이외의 phone1 값이 들어오면 오류를 내뿜게 된다.

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

 

기본값 정의

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

예를 들어, 키를 입력하지 않는다면 기본적으로 160이라고 입력되도록 하고 싶다면 아래와 같이 정의한다.

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문을 사용해서 기본값을 정의하려면 아래처럼 쿼리를 작성한다.

ALTER TABLE member ALTER COLUMN phone1 SET DEFAULT '02';

 

이제 데이터를 입력하면

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

default가 들어간 열은 height과 phone1이다.

기본 값을 정의했기 때문에 height에는 160이 들어가고, phone1에는 02가 들어간다.

 

널 값 허용

NULL 값을 허용하지 않으면 생략하거나 NULL을 사용하고, 허용하지 않으려면 NOT NULL을 사용한다.

다만, PRIMARY KEY가 설정된 열에는 NULL 값이 있을 수 없으므로 생략하면 자동으로 NOT NULL이 적용된다.

NULL값은 아무것도 없다는 의미이다. 공백(' ')이나 0과는 다르다.