CONSTRAINT 와 CASCADE 의 활용

FOREIGN KEY(외래키) 란?

A 와  B라는 테이블이 존재 한다고 하면,

A테이블의 어떤 KEY 를 참조 하여 B 테이블에서 사용하고자 할때 이 KEY를 Foreign key (외래키)라 할 수 있다.

예를 들어

이렇게 users 테이블 과 comments 테이블이 있다고 하면

users 테이블의 user_no 는 PK 즉 Primary Key (기본키) 이고,

comments 테이블의 comment_no 는 PK 이며,

user_no 를 FK 즉 Foreign key (외래키)로 사용하여 부모테이블인 users 의 user_no를 이용할 수 있다.

이렇게 외래키를 사용 하면 어떤 점이 좋을까?

commnets 테이블에 user_no 는 users 테이블의 user_no에 존재 하지 않는 값은 들어 갈수가 없다.

이렇게 참조무결성(referential integrity)을 유지 할 수 있다.

일단 외래키를 이용하여 테이블을 생성 해 보자.

CREATE TABLE users (
  user_no int(5) NOT NULL AUTO_INCREMENT,
  user_name varchar(50) DEFAULT '',
  PRIMARY KEY (user_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE comments (
  comment_no int(5) NOT NULL AUTO_INCREMENT,
  user_no int(5) NOT NULL,
  comment_text varchar(200) DEFAULT '',
  PRIMARY KEY (comment_no),
  CONSTRAINT comments_fk_1 FOREIGN KEY (user_no) REFERENCES users (user_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

users 테이블에 테스트용 데이터를 입력한다.

INSERT INTO users (user_name)
VALUES 
('test1'),('test2'),('test3');

select * from users;
+---------+-----------+
| user_no | user_name |
+---------+-----------+
|       1 | test1     |
|       2 | test2     |
|       3 | test3     |
+---------+-----------+

이제 comments 테이블에 데이터를 입력 한다.

INSERT INTO comments VALUES ('comment1');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`comments`, CONSTRAINT `comments_fk_1` FOREIGN KEY (`user_no`) REFERENCES `users` (`user_no`))

FK인 user_no는 null 일 수 없다.

null이 아닌 값을 입력 해 보자.

INSERT INTO comments (user_no, comment_text) VALUES (4, 'comment test1') ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`comments`, CONSTRAINT `comments_fk_1` FOREIGN KEY (`user_no`) REFERENCES `users` (`user_no`))

FK인 user_no는 부모테이블인 users의 user_no 칼럼에 들어 있지 않은 값은 입력 할 수가 없다.

users 테이블에 존재 하는 user_no 로 입력하자.

INSERT INTO comments (user_no, comment_text)
VALUES
(1, 'comment test1'),
(1, 'comment test2'),
(2, 'comment test3')  ;
Query OK, 3 rows affected (0.00 sec)

SELECT * FROM comments;
+------------+---------+---------------+
| comment_no | user_no | comment_text  |
+------------+---------+---------------+
|          1 |       1 | comment test1 |
|          2 |       1 | comment test2 |
|          3 |       2 | comment test3 |
+------------+---------+---------------+
3 rows in set (0.00 sec)

users의 존재하는 user_no를 입력 할 때 입력이 가능 하다.

이 부분은 업데이트시 에도 동일하게 적용 된다.

UPDATE comments
    -> SET user_no = 4
    -> WHERE comment_no = 1;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`comments`, CONSTRAINT `comments_fk_1` FOREIGN KEY (`user_no`) REFERENCES `users` (`user_no`))

UPDATE comments
    -> SET user_no = 2
    -> WHERE comment_no = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM comments;
+------------+---------+---------------+
| comment_no | user_no | comment_text  |
+------------+---------+---------------+
|          1 |       2 | comment test1 |
|          2 |       1 | comment test2 |
|          3 |       2 | comment test3 |
+------------+---------+---------------+
3 rows in set (0.00 sec)

이제 users 테이블의 경우는 어떻게 될까?

UPDATE users
    -> SET user_no = 4
    -> WHERE user_no = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`comments`, CONSTRAINT `comments_fk_1` FOREIGN KEY (`user_no`) REFERENCES `users` (`user_no`))

DELETE FROM users
    -> WHERE user_no = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`comments`, CONSTRAINT `comments_fk_1` FOREIGN KEY (`user_no`) REFERENCES `users` (`user_no`))

참조되고 있는 값의 정합성이 깨지기 때문에 업데이트와 삭제 모두 제한되어 있다.

여기서 comments 테이블에서 사용 되지 않고 있는 row를 삭제 해 보자.

UPDATE users
    -> SET user_no = 4
    -> WHERE  user_no = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

DELETE FROM users WHERE user_no = 4;
Query OK, 1 row affected (0.00 sec)

참조 되지 않고 있는 값은 수정, 삭제가 가능하다.

테이블을 생성했던 SQL을 보면

CREATE TABLE comments (
  comment_no int(5) NOT NULL AUTO_INCREMENT,
  user_no int(5) NOT NULL,
  comment_text varchar(200) DEFAULT '',
  PRIMARY KEY (comment_no),
  CONSTRAINT comments_fk_1 FOREIGN KEY (user_no) REFERENCES users (user_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CONSTRAINT comments_fk_1 FOREIGN KEY (user_no) REFERENCES users (user_no)

제약조건명과 참조 테이블과 참조칼럼을 지정 하고 별도의 옵션을 주지 않았다.

별도의 옵션이 없는 경우 아래 두가지 기본 옵션이 적용된다.

ON DELETE RESTRICT - 삭제시 제약

ON UPDATE RESTRICT - 갱신시 제약

CASCADE의 활용

제약조건을 사용하여, 부모 테이블인 users의 데이터가 삭제나 변경시 오류가 아닌 자식 테이블인 commnents 테이블의 데이터에도 적용되게 해 보자.

현재 테이블의 제약조건을 확인해 보자.

select * from information_schema.table_constraints
    -> where TABLE_NAME = 'comments';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | test              | PRIMARY         | test         | comments   | PRIMARY KEY     |
| def                | test              | comments_fk_1   | test         | comments   | FOREIGN KEY     |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
2 rows in set (0.00 sec)

comments 테이블에는 2개의 제약조건이 걸려 있는 것을 확인 할 수 있다.

이제 외래키 관련 된 제약조건을 삭제 후 재 생성 해 보자.

ALTER TABLE comments  DROP CONSTRAINT comments_fk_1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CONSTRAINT comments_fk_1' at line 1

일반 제약조건은

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

으로 삭제가 가능하지만 외래키 관련 제약조건의 경우는

ALTER TABLE [테이블명] DROP FOREIGN KEY [제약조건명] ;

으로 삭제가 가능하다.

ALTER TABLE  comments  DROP FOREIGN KEY comments_fk_1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

select * from information_schema.table_constraints
    -> where TABLE_NAME = 'comments';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | test              | PRIMARY         | test         | comments   | PRIMARY KEY     |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
1 row in set (0.00 sec)

이제 CASCADE가 추가 된 제약조건을 재 생성 해보자.

ALTER TABLE comments
    -> ADD CONSTRAINT comments_fk_1
    -> FOREIGN KEY(user_no) REFERENCES users(user_no)
    -> ON DELETE CASCADE
    -> ON UPDATE CASCADE ;
Query OK, 3 rows affected (0.04 sec)               
Records: 3  Duplicates: 0  Warnings: 0

select * from information_schema.table_constraints
    -> where TABLE_NAME = 'comments';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | test              | PRIMARY         | test         | comments   | PRIMARY KEY     |
| def                | test              | comments_fk_1   | test         | comments   | FOREIGN KEY     |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
2 rows in set (0.00 sec)

변경된 제약조건을 테스트 해 보자.

현재 데이터를 확인 해 보자.

SELECT * FROM users;
+---------+-----------+
| user_no | user_name |
+---------+-----------+
|       1 | test1     |
|       2 | test2     |
+---------+-----------+
2 rows in set (0.00 sec)

SELECT * FROM comments;
+------------+---------+---------------+
| comment_no | user_no | comment_text  |
+------------+---------+---------------+
|          1 |       2 | comment test1 |
|          2 |       1 | comment test2 |
|          3 |       2 | comment test3 |
+------------+---------+---------------+
3 rows in set (0.00 sec)

그리고 업데이트를 수행하면

UPDATE users
    -> SET user_no = 3
    -> WHERE user_no = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM users;
+---------+-----------+
| user_no | user_name |
+---------+-----------+
|       1 | test1     |
|       3 | test2     |
+---------+-----------+
2 rows in set (0.00 sec)

데이터는 변경 된다.

이때 comments 테이블은 어떻게 되었을까?

SELECT * FROM comments;
+------------+---------+---------------+
| comment_no | user_no | comment_text  |
+------------+---------+---------------+
|          1 |       3 | comment test1 |
|          2 |       1 | comment test2 |
|          3 |       3 | comment test3 |
+------------+---------+---------------+

해당 user_no도 변경 되었다.

이번에는 삭제를 해 보자.

DELETE FROM users WHERE user_no = 3;
Query OK, 1 row affected (0.00 sec)

SELECT * FROM users;
+---------+-----------+
| user_no | user_name |
+---------+-----------+
|       1 | test1     |
+---------+-----------+
1 row in set (0.00 sec)

SELECT * FROM comments;
+------------+---------+---------------+
| comment_no | user_no | comment_text  |
+------------+---------+---------------+
|          2 |       1 | comment test2 |
+------------+---------+---------------+
1 row in set (0.00 sec)

users 테이블의 해당 데이터와 comments 테이블의 참조 데이터도 모두 삭제 되었다.

이상.