SQL 첫걸음 27강. 제약

제약

• CREATE TABLE로 테이블을 정의할 때 NOT NULL과 같은 제약 또한 정의할 수 있습니다.
• 이러한 제약은 저장될 데이터를 단어 의미 그대로 제한하는 역할을 합니다.
• NOT NULL 외에도 대표적인 제약조건으로는 기본기(Primary Key) 제약이나 외부참조(정합) 제약 등이 있습니다.

> ALTER TABLE 테이블명 ADD 추가할 컬럼명 데이터 유형;

> ALTER TABLE 테이블명 DROP COLUMN 삭제할 컬럼명;

> ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터 유형 [DEFAULT 식] [NOT NULL],
                             컬럼명2 데이터 유형 [DEFAULT 식] [NOT NULL]);
                             
> ALTER TABLE 테이블명 RENAME COLUMN 변경해야할 컬럼명 TO 새로운 컬럼명;

> ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);

> ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

---

테이블 작성시 제약 정의

• NOT NULL 제약조건을 가진 name열과 NOT NULL 제약조건 및 UNIQUE 제약조건을 가진 no열로 구성된 테이블 sample531을 만드는 방법은 아래와 같습니다.

mysql > CREATE TABLE sample531 (
    -> no INTEGER NOT NULL UNIQUE,
    -> name VARCHAR(8) NOT NULL
    -> );

Query OK, 0 rows affected (0.01 sec)

mysql > DESC sample531;

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| no    | int        | NO   | PRI | NULL    |       |
| name  | varchar(8) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

이처럼 열에 대해 정의하는 제약을 열 제약이라 합니다.

NOT NULL 제약조건 및 기본키(PRIMARY KEY) 제약조건을 가진 no열과 sub_no열, 그리고 아무런 제약조건을 가지고 있지 않은 name열로 구성된 테이블 sample632를 만드는 방법은 아래와 같습니다.

mysql > CREATE TABLE sample632 (
    -> no INTEGER NOT NULL,
    -> sub_no INTEGER NOT NULL,
    -> name VARCHAR(8),
    -> PRIMARY KEY (no, sub_no)
    -> );

Query OK, 0 rows affected (0.01 sec)

mysql > DESC sample632;

+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| no     | int        | NO   | PRI | NULL    |       |
| sub_no | int        | NO   | PRI | NULL    |       |
| name   | varchar(8) | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

이처럼 한 개의 제약으로 복수의 열에 제약을 설정하는 경우를 테이블 제약이라 합니다.

• 제약에는 이름을 붙일 수 있습니다. 이는 추후 관리를 쉽게 하기 위해서입니다.
• 이때 CONSTRAINT 키워드를 사용하여 제약의 이름을 지정할 수 있습니다.
• 그 방법은 아래와 같습니다. 키워드 다음에 오는 문자열이 곧 제약의 이름이 됩니다.

mysql > CREATE TABLE sample631 (
    -> no INTEGER NOT NULL,
    -> CONSTRAINT pkey_sample631 PRIMARY KEY (no)
    -> );

Query OK, 0 rows affected (0.01 sec)

mysql > DESC sample631;

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| no    | int  | NO   | PRI | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

---

제약 추가

• 기존에 존재하던 테이블에도 나중에 제약을 따로 추가할 수 있습니다.
• 이때 열 제약과 테이블 제약이라는 두 가지 방법이 존재한다는 걸 앞서 알게 되었습니다.

열 제약 추가

ALTER TABLE로 열 정의를 변경할 때 MODIFY 하부명령을 통해 해당 열에 제약조건을 추가할 수 있습니다.

이때 유의할 점은 기존 존재하던 데이터에 해당 제약조건이 영향을 끼치기 때문에 만약 제약을 위반하는 데이터가 있다면 오류가 발생합니다.

테이블 제약 추가

• 테이블 제약은 ALTER TABLE 명령의 ADD 하부명령으로 추가할 수 있습니다.
• 이때 유의할 점은 기본키의 경우 테이블에 하나만 설정할 수 있기 때문에 이미 테이블에 기본키가 설정되어 있다면 추가로 기본키를 작성할 수 없다는 것입니다.
• 또한 열 제약 때와 마찬가지로 기존의 데이터에 제약조건을 위반하는 경우가 있다면 오류가 발생합니다.

예를 들면 아래 명령문과 같은 방식으로 테이블 제약을 추가할 수 있습니다.

ALTER TABLE sample631 ADD CONSTRAINT pkey_sample631 PRIMARY KEY (no);

제약 삭제

• 열 제약은 열 정의를 변경하는 방식으로 변경할 수 있고 테이블 제약은 삭제할 수 있습니다.

• 먼저 열 제약을 삭제하는 방법은 예를 들어 기존 테이블 smple631에 NOT NULL 제약조건이 있던 c열의 제약조건을 없애는 방법은 아래 명령문과 같습니다.

ALTER TABLE sample631 MODIFY c VARCHAR(30);

• 테이블 제약의 경우 ALTER TABLE 명령의 DROP 하부명령으로 삭제할 수 있습니다.
• 이때 MySQL의 경우 기본키는 테이블 하나당 설정할 수 있기 때문에 PRIMARY KEY라는 키워드를 입력해 삭제할 수 있고 그밖의 데이터베이스 제품은 삭제할 때 지정했던 제약명을 입력하여 삭제합니다.

mysql > ALTER TABLE sample631 DROP PRIMARY KEY;

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > DESC sample631;

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| no    | int  | NO   |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

• 다른 데이터베이스 제품의 경우 테이블 제약을 삭제하는 방법은 아래와 같이 해당 제약명을 입력하면 됩니다.

ALTER TABLE sample631 DROP CONSTRAINT pkey_sample631;

---

기본키

• 기본키로 설정할 열은 NOT NULL 및 UNIQUE 제약조건이 선행되어야 합니다.

• 따라서 기본키로 설정된 열이 NULL 값을 가지거나 중복하는 데이터 값을 가지면 제약에 위반됩니다.

• 아래와 같은 방법으로 만든 테이블 sample634가 존재한다고 가정해봅시다.

mysql > CREATE TABLE sample634 (
    -> no INTEGER NOT NULL,
    -> name VARCHAR(8),
    -> CONSTRAINT pkey_sample634 PRIMARY KEY (no)
    -> );

Query OK, 0 rows affected (0.01 sec)

• 그리고 아래와 같이 테이블에 두 개의 행을 추가했다고 가정해봅시다.

INSERT INTO sample634 VALUES(1, '첫번째 행');
INSERT INTO sample634 VALUES(2, '두번째 행');

• 이때 기본키로 설정된 no열에 중복된 값을 추가하는 시도를 하면 아래와 같이 오류가 발생합니다.

mysql > INSERT INTO sample634 VALUES(2, '중복된 행');

ERROR 1062 (23000): Duplicate entry '2' for key 'sample634.PRIMARY'

• 이는 UPDATE 명령에도 똑같이 적용되어 제약조건에 위반이 되면 오류가 발생합니다.

mysql > UPDATE sample634 SET no = 2 WHERE no = 1;

ERROR 1062 (23000): Duplicate entry '2' for key 'sample634.PRIMARY'

• 이처럼 열을 기본키로 지정해 유일한 값을 가지도록 하는 구조가 기본키 제약입니다. 행이 유일성을 필요로 한다는 의미에서 유일성 제약이라 부르기도 합니다.

복수의 열로 기본키 구성하기

• 기본키 제약에는 이를 구성할 열 지정이 필요하며 이때 지정된 열은 NOT NULL 제약이 설정되어 있어야 합니다. 다시 말해 기본키로는 NULL 값이 허용되지 않습니다.

• 이때 기본키를 구성하는 열은 복수여도 무관합니다. 따라서 아래와 같이 a열과 b열이 모두 기본키인 테이블 sample635의 경우 하나의 열만 봤을 때는 중복되는 값이 존재하기 때문에 기본키 제약에 위반되지만 a열과 b열을 하나의 쌍으로 봤을 때는 (1, 1), (1, 2)와 같이 전부 중복되지 않는 쌍이기 때문에 기본키 제약에 위반되지 않습니다.

+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
+---+---+