[혼공SQL] 제약 조건

 

제약 조건이란 데이터의 무결성(데이터에 결함이 없음)을 지키기 위해 제한하는 조건을 의미합니다.

 

이러한 제약 조건에는 PK 제약조건, FK 제약조건, UNIQUE 제약조건, CHECK 제약 조건, DEFAULT 제약 조건, NULL값 허용이 있습니다. 오늘은 이들의 제약조건에 대해서 알아보겠습니다.

 

 

  PK 제약조건

테이블의 열에 PK 제약조건을 추가할 수 있다.

 

PK(Primary Key)는 데이터를 구분할 수 있는 식별자를 의미하며 이는

• 중복될 수 없고
• NULL이 될 수 없다

는 특징이 있다.

 

이렇게 기본키로 생성한 데이터는 자동으로 클러스터형 인덱스가 생성된다고 한다. 클러스터형 인덱스에 관해서는 나중에 다룬다.

 

또한 테이블에서는 기본 키를 하나만 가질 수 있다. 테이블의 특성을 가장 잘 반영하는 열을 선택하는 것이 중요하다.

 

   PK 제약조건 설정하기

1. CREATE TABLE 문에서 설정하기 - 1

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

 

2. CREATE TABLE 문에서 설정하기 - 2

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

 

3. ALTER TABLE 문에서 설정하기

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

 

 

  FK 제약조건

FK(Foreign Key)는 두 테이블 사이의 관계를 연결해줌으로써 데이터의 무결성을 보장해준다.

 

FK 제약조건에서는 외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결되어 있어야 한다.

또한 기본 키가 있는 테이블을 '기준 테이블'이라고 하며, 외래 키가 있는 테이블은 '참조 테이블'이라고 한다.

 

예를 들어 회원 테이블과 구매 테이블을 예로 들어보자. 회원 테이블의 mem_id와 구매 테이블의 mem_id는 기본 키 -외래 키 관계로 맺어줄 수 있다. 여기서 회원 테이블의 mem_id가 PK라면, 해당 테이블이 기준 테이블이 되며, 구매 테이블이 참조 테이블이 된다.

 

 

• 참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나 고유 키여야 한다.

 

   FK 제약조건 설정하기

1. CREATE TABLE에서 설정하기

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

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

위 예시와 같이, 

FOREIGN KEY(참조_테이블_열_이름) REFERENCES 기준_테이블_이름(기준_테이블_열_이름)

해당 형식으로 작성해주면 FK 제약조건이 설정된다.

 

2. ALTER TABLE로 설정하기

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

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

 

 

   FK 제약조건의 수정, 삭제

외래 키 제약조건은 기준 테이블의 값이 변경될 때 효과를 발휘한다.

 

가령, member 테이블의 mem_id가 '안녕'이라는 값의 데이터가 있다고 하자. 이 데이터로 buy 테이블에 2개의 데이터가 존재한다.

그리고 member 테이블에서 mem_id='안녕'인 데이터를 '하이'로 바꾼다면 어떻게 될까?

 

buy 테이블에서의 데이터는 수정되지 않았으므로 buy 테이블에 해당하는 member 테이블의 값을 찾아오지 못하게 된다.

 

이는 데이터의 수정뿐만이 아니라 삭제도 마찬가지이다.

 

 

이는 데이터의 무결성이 보장되지 않는다. 따라서 우리는 이러한 상황에서 기준 테이블의 값이 변경될 때, 참조 테이블에서도 동시에 해당 값의 변경 상태를 반영해주는 'ON UPDATE CASCADE'와 'ON DELETE CASCADE'를 사용할 수 있다.

 

앞에서 소개된 member 테이블과 buy 테이블이 있다고 할 때, 우리는 아래와 같이 buy 테이블에 제약조건을 추가해줄 수 있다.

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

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

이렇게 설정하게 되면, 기준 테이블의 데이터가 수정, 삭제될 때 참조 테이블의 데이터 또한 함께 바뀌게 되므로 기준 테이블의 값 변경시 고려할 사항이 줄어든다.

 

 

  기타 제약조건

PK, FK 제약 조건 이외에도 앞서 소개한 바와 같이 UNIQUE, CHECK, DEFAULT, NULL 허용이 있다. 이를 알아보자.

 

   고유(UNIQUE) 키 제약조건

FK 제약조건에서 기준 테이블의 열은 PK 혹은 UNIQUE만 가능하다고 언급한 적이 있다. 

 

고유 키 제약조건이란 중복되지 않는 유일한 값을 입력해야한다는 조건으로 PK와 비슷하지만 UNIQUE는 NULL을 허용한다는 차이점이 존재한다.

 

고유 키 제약조건은 아래와 같이 UNIQUE 키워드를 통해서 설정할 수 있다.

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

 

   체크 제약조건

체크는 데이터가 입력될 시 조건에 부합하는 지를 확인할 수 있다.

 

아래 예시를 보면 height가 0보다 큼을 조건으로 지정하였고, 만약 height 열의 값에 0 이하의 값이 들어오게 된다면 오류를 발생시킨다.

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

 

당연하게도, 테이블이 생성된 이후라도 ALTER 문을 사용하여 체크 제약조건을 추가할 수 있다.

ALTER TABLE member
	ADD CONSTRAINT
	CHECK (phone1 IN('02', '031', '063'));

이를 통해 phone1의 열에 02, 031, 063에 해당되는 값만 들어올 수 있게 지정해줄 수 있는 것이다.

 

   기본값(DEFAULT) 정의

이는 값을 입력하지 않을 때, 자동으로 입력될 값을 정해놓을 수 있다.

 

아래와 같이 작성하게 된다면 mem_name이 NULL로 입력될 때, '익명'으로 데이터가 삽입되게 된다.

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

 

위와 동일하게 ALTER 문으로 테이블이 생성된 이후 기본값을 정의할 수도 있다.

ALTER TABLE member
	ALTER COLUMN height SET DEFAULT 160;

 

정의한 기본값을 삽입하려면 데이터 삽입 당시 해당 열의 데이터를 default로 지정해주면 된다.

INSERT INTO member VALUES('APK', '에이핑크', default);

 

   NULL 값 허용

우리가 테이블에 선언하였을 때의 NULL과 동일하다. 해당 열에 NULL값이 들어갈 수 있음을 의미한다. NOT NULL로 NULL이 들어갈 수 없음을 선언할 수도 있다.