22. MySQL 테이블과 뷰 - 제약조건기본키, 외래키

샘플로 사용할 tabledb

제약 조건

데이터의 무결성을 지키기 위한 제한된 조건을 의미한다. 즉, 특정 데이터를 입력할 때 무조건적으로 입력되는 것이 아닌, 어떠한 조건을 만족했을 때에 입력되도록 제약할 수 있다.
간단한 예로, 인터넷 쇼핑몰에 회원 가입을 해본 경험이 있을 것이다. 그런데, 만약 여러분이 동일한 아이디로 다시 회원 가입을 하면 회원 가입이 안 된다. 그 이유는 아이디 열은 동일한 것이 들어갈 수 없는 제약 조건이 설정되어 있기 때문이다.
이 외에도 제약 조건은 많은 것이 있으며, 지금까지 실습 중에 하나 둘씩 나왔었다. 이제는 제약 조건을 체계적으로 정리해 보자.
MySQL은 데이터의 무결성을 위해서 다음의 5가지의 제약 조건을 제공한다.
PRIMARY KEY 제약 조건
FOREIGN KEY 제약 조건
UNIQUE 제약 조건
CHECK 제약 조건
DEFAULT 정의
NULL 값 허용

기본 키 제약 조건

테이블에 존재하는 많은 행의 데이터를 구분할 수 있는 식별자를 '기본 키'라고 부른다. 예로 회원 테이블의 회원 아이디, 학생 테이블의 학번 등이 이에 해당한다.
기본 키에 입력되는 값은 중복될 수 없으며 NULL값이 입력될 수 없다. 인터넷 쇼핑몰에 회원 가입한 것을 기억하자. 대부분의 인터넷 쇼핑몰에서는 회원 테이블의 기본 키를 회원 아이디로 설정해 놓았을 것이다.
!설계 방법에 따라서 회원 아이디가 기본 키가 아닐 수도 있다. 그리고 지금 필자가 얘기하는 것은 보편적인 경우다. 회원 아이디가 아닌 주민등록번호나 Email 또는 휴대폰 번호로 회원을 구분하는 사이트도 종종 있다.

회원 가입 시에 생성하는 회원 아이디가 중복된 것을 본적이 있는가? 또, 회원 아이디 없이 회원 가입이 되는가? 아마도 없을 것이다. 이는 회원 아이디가 기본 키로 설정되어 있기 때문이다.
기본 키는 테이블에서 중요한 의미를 갖는다. 우선, 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다. (인덱스에 대한 얘기는 chapter 09에서 살펴보겠다.) 또한, 테이블에서는 기본키를 하나 이상의 열에 설정할 수 있다. 즉, 회원 아이디와 같이 하나의 열에만 기본 키를 설정할 수도 있고, 두 개의 열을 합쳐서 기본 키로 설정할 수도 있다.

대부분의 테이블은 기본 키를 가져야 한다. 물론, 기본 키가 없어도 테이블의 구성이 가능하지만 실무적으로는 대부분의 테이블에는 기본 키를 설정해줘야 한다고 생각하자.

기본 키를 생성하는 방법은 앞에서 실습했던 CREATE TABLE문에 PRIMARY KEY라는 예약어를 넣어주면 된다.

USE tableDB;
DROP TABLE IF EXISTS buytbl, usertbl;
CREATE TABLE userTBL
( userID	CHAR(8) NOT NULL PRIMARY KEY,
  name		VARCHAR(10) NOT NULL,
  birthYear	INT NOT NULL
);

이렇게 설정함으로써 회원 아이디(userID)는 회원 테이블(usertbl)의 기본 키가 되었으며 앞으로 입력되는 회원 아이디는 당연히 중복될 수도 없고, 비어(NULL)있을 수도 없다.

그런데, 모든 제약 조건은 이름을 가지게 되는데, 이렇게 CREATE TABLE 구문 안에서 기본 키를 지정하면 제약 조건의 이름은 MySQL이 알아서 설정해 준다. 일반적으로 PRIMARY KEY의 이름을 알 필요는 없으며 'usertbl 테이블에 Primary Key로 지정된 것'정도로 파악이 충분히 가능할 것이다.
테이블의 정보를 보기 위해서는 DESCRIBE문을 사용하면 된다.

DESCRIBE usertbl;

필요하다면 PRIMARY KEY를 지정하면서 키의 이름까지 직접 지어줄 수가 있다. 예로 PK_userTBL_userID와 같은 이름을 붙여 주면 이름만으로도 'PK가 userTBL 테이블의 userID열에 지정됨'을 읽을 수 있다. 그러기 위해서는 다음과 같이 사용하면 된다.

DROP TABLE IF EXISTS userTBL;
CREATE TABLE userTBL
( userID	CHAR(8) NOT NULL,
  name		VARCHAR(10) NOT NULL,
  birthYear INT NOT NULL,
  CONSTRAINT PRIMARY KEY PK_userTBL_userID (userID)
);

*MySQL의 Primary Key 이름
MySQL은 Primary Key로 지정하면 항상 키 이름을 'PRIMARY'로 보여준다. 그러므로 지금과 같이 이름을 Primary Key의 이름을 직접 지정하는 것이 별 의미는 없다. 하지만 Foreign Key는 하나의 테이블에 여러 개가 생성될 수 있으므로 이름을 지정해서 관리하는 것이 편리하다. 참고로 테이블에 지정된 키를 보려면 SHOW KEYS FROM 테이블이름; 구문을 사용하면 된다.

위 구문에서 CONSTRAINT는 생략해도 된다. 만약 기본 키의 이름을 지정할 필요가 없다면 제일 마지막 행에 간단히 'PRIMARY KEY (userID)'만 써줘도 된다.
제약 조건을 설정하는 또 다른 방법은 이미 만들어진 테이블을 수정하는 ALTER TABLE 구문을 사용하는 것이다. 다음과 같이 사용할 수 있다.

DROP TABLE IF EXISTS userTBL;
CREATE TABLE userTBL
( userID	CHAR(8) NOT NULL,
  name		VARCHAR(10) NOT NULL,
  birthYear	INT NOT NULL
);
ALTER TABLE userTBL
	ADD CONSTRAINT PK_userTBL_userID
    	PRIMARY KEY (userID);

CREATE TABLE 안에 PRIMARY KEY문으로 설정한 것과 나중에 ALTER TABLE로 PRIMARY KEY를 지정하는 것은 동일하다.

• ALTER TABLE usertbl
  usertbl을 변경하자.
• ADD CONSTRAINT PK_usertbl_userID
  제약 조건을 추가하자. 추가할 제약 조건 이름은 'PK_usertbl_userID'이다.
• PRIMARY KEY (userID)
  추가할 제약 조건은 기본 키 제약 조건이다. 그리고 제약 조건을 설정할 열은 userID열이다.

만약 제품코드 AAA가 냉장고, BBB가 세탁기, CCC가 TV라고 가정한다면 현재 제품코드만으로는 중복이 될 수밖에 없으므로, 기본 키로 설정할 수가 없다. 또한, 제품 일련 번호도 마찬가지로 각제품 별로 0001번부터 부여하는 체계라서 기본 키로 설정할 수 없다.
이러한 경우에는 '제품코드 + 제품일련번호'를 합친다면 유일한 값이 될 수 있으므로 기본 키로 사용할 수 있다.

DROP TABLE IF EXISTS prodTbl;
CREATE TABLE prodTbl
( prodCode CHAR(3) NOT NULL,
  prodID   CHAR(4) NOT NULL,
  prodDate DATETIME NOT NULL,
  prodCur  CHAR(10) NULL
);
ALTER TABLE prodTbl
	ADD CONSTRAINT PK_prodTbl_proCode_prodID
    	PRIMARY KEY (prodCode, prodID);

또는 CREATE TABLE 구문 안에 직접 사용할 수도 있다. 마지막 열 이후에 콤마(,)로 분리하고 제약 조건을 직접 지정하면 된다.

DROP TABLE IF EXISTS prodTbl;
CREATE TABLE prodTbl
( prodCode CHAR(3) NOT NULL,
  prodID   CHAR(4) NOT NULL,
  prodDate DATETIME NOT NULL,
  prodCur  CHAR(10) NULL
  CONSTRAINT PK_prodTbl_proCode_prodID
  	PRIMARY KEY (prodCode, prodID)
);

SHOW INDEX FROM prodTbl문으로 테이블의 정보를 확인하면 두 열이 합쳐져서 하나의 기본 키 제약 조건을 설정하고 있음이 확인된다.

두 열을 하나의 기본 키로 설정한 상태임
실무에서도 종종 발생되는 형태이므로 잘 기억해 두자.

외래 키 제약 조건

두 테이블 사이의 관계를 선언함으로써 데이터의 무결성을 보장해 주는 역할을 한다. 외래 키 관계를 설정하면 하나의 테이블이 다른 테이블에 의존하게 된다.

초보자의 경우에 외래 키를 정의하는 테이블과 외래 키가 참조하는 테이블을 가끔 혼동하는 경우가 있다.

쉽게 외래 키를 정의하는 테이블인 buytbl을 '외래 키 테이블'이라고 부르고, 외래 키에 의해서 참조가 되는 테이블인 usertbl을 그냥 '기준 테이블'이라고 부르면 좀 더 직관적으로 이해하기가 쉬워진다.

우선 외래 키 테이블에 데이터를 입력할 때는 꼭 기준 테이블을 참조해서 입력하므로 기준 테이블에 이미 데이터가 존재해야 한다. 앞의 실습에서 buytbl에 JYP(조용필)가 입력이 안되던 것을 확인했다. 이것은 외래 키 제약 조건을 위반했기 때문이다.

또, 외래 키 테이블이 참조하는 기존 테이블의 열은 반드시 Primary Key이거나 Unique 제약 조건이 설정되어 있어야 한다. Unique 제약 조건은 잠시 후에 살펴보겠다.

외래 키를 생성하는 방법은 CREATE TABLE 끝에 FOREIGN KEY 키워드로 설정하는 방법이 있다.

DROP TABLE IF EXISTS buyTBL, userTBL;
CREATE TABLE userTBL
( userID	CHAR(8) NOT NULL PRIMARY KEY,
  name		VARCHAR(10) NOT NULL,
  birthYear	INT NOT NULL
);
CREATE TABLE buyTBL
( num	INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  userID	CHAR(8) NOT NULL,
  prodName	CHAR(6) NOT NULL,
  FOREIGN KEY(userID) REFERENCES userTBL(userID)
);

위 예에서 보면 외래 키 테이블(buytbl)의 열(userid)에서 참조(references)하는 기준 테이블(usertbl)의 열(userID)은 기본 키로 설정되어 있는 것이 확인된다. 만약, 기준 테이블이 Primary Key 또는 Unique가 아니라면 외래 키 관계는 설정되지 않는다.
마찬가지로 직접 외래 키의 이름을 지정하기 위해서는 제일 아래에 다음과 같이 써주면 된다.

DROP TABLE IF EXISTS buyTBL;
CREATE TABLE buyTBL
( num	INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  userID	CHAR(8) NOT NULL,
  prodName	CHAR(6) NOT NULL,
  CONSTRAINT FK_userTBL_buyTBL FOREIGN KEY(userID) REFERENCES userTBL(userID)
);

위의 예에서는 외래 키 제약 조건의 이름을 FK_usertbl_buytbl로 설정했다. 만약 외래 키의 이름을 지정할 필요가 없다면 제일 마지막 행에 간단히 FOREIGN KEY(userID) REFERENCES userTBL(userID)만 써줘도 된다.

DROP TABLE IF EXISTS buyTBL;
CREATE TABLE buyTBL
( num	INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  userID	CHAR(8) NOT NULL,
  prodName	CHAR(6) NOT NULL
);
ALTER TABLE buyTBL
	ADD CONSTRAINT FK_userTBL_buyTBL
    FOREIGN KEY (userID)
    REFERENCES userTBL(userID);

• ALTER TABLE buytbl
  buytbl을 수정한다.
• ADD CONSTRAINT FK_usertbl_buytbl
  제약 조건을 더한다. 제약 조건 이름은 'FK_usertbl_buytbl'로 명명한다.
• FOREIGN KEY(userID)
  외래 키 제약 조건을 buytbl의 userID에 설정한다.
• REFERENCES usertbl(userID)
  참조할 기준 테이블은 usertbl 테이블의 userID열이다.

설정된 외래 키 제약 조건은 마찬가지로 SHOW INDEX FROM buytbl문으로 확인할 수 있다.

외래 키의 옵션 중에 ON DELETE CASCASE 또는 ON UPDATE CASCADE가 있는데, 이는 기준 테이블의 데이터가 변경되었을 때 외래 키 테이블도 자동으로 적용되도록 설정해 준다.

예로 ON UPDATE CASCADE로 설정하면 회원 테이블의 김점수의 ID인 KBS가 Kim으로 변경될 경우에, 구매 테이블의 KBS도 Kim으로 자동 변경된다.

ALTER TABLE buytbl
	DROP FOREIGN KEY FK_usertbl_buytbl; -- 외래 키 제거
ALTER TABLE buytbl
	ADD CONSTRAINT FK_usertbl_buytbl
    FOREIGN KEY (userID)
    REFERENCES usertbl (userID)
    ON UPDATE CASCADE;

별도로 지정하지 않으면 ON UPDATE NO ACTION 및 ON DELETE NO ACTION을 지정한 것과 동일하다. 즉, 회원 테이블의 회원 아이디가 변경되어도 아무런 일이 일어나지 않는다는 의미다.