MySQL 외래키 옵션 변경

MySQL 외래키 옵션 변경

1 예제

MySQL 외래키 생성의 예제를 그대로 활용한다.

mysql> select * from dept;
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
|    1001 | 인사부    | 
|    1002 | 영업부    | 
|    1003 | 생산부    | 
+---------+-----------+

mysql> select * from emp; 
+---------+---------+----------+
| emp_no  | dept_no | emp_name |
+---------+---------+----------+
| 2012001 |    1001 | 한놈     | 
| 2012002 |    1003 | 두시기   | 
| 2012003 |    1003 | 석삼     | 
+---------+---------+----------+

2 제약조건 확인

MySQL 제약조건 확인 문서를 참고하십시오.

mysql> show create table emp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `emp_no` int(11) unsigned NOT NULL,
  `dept_no` int(11) unsigned NOT NULL,
  `emp_name` varchar(32) NOT NULL,
  PRIMARY KEY  (`emp_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+

→ emp 테이블에 걸려 있는 제약조건은 1개이며, 이름은 emp_ibfk_1이다.

→ emp의 dept_no는 외래키이며, dept의 dept_no를 참조하고 있다. 별도의 옵션이 없으므로 DELETE시, UPDATE시에 제약(RESTRICT)이 있다.

3 변경 시도 1차

기존 예제에서는 별도의 옵션을 주지 않았으므로 다음과 같이 기본값이 적용되어 있다.

ON DELETE RESTRICT - 삭제시 제약
ON UPDATE RESTRICT - 갱신시 제약

참조되고 있는 값을 삭제 또는 변경하려고 하면, 자료의 정합성이 깨지게 되므로 다음과 같이 오류가 발생된다.

mysql> DELETE FROM dept WHERE dept_no='1003';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`))

mysql> UPDATE dept SET dept_no='1004' WHERE dept_no='1003';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`))

4 CASCADE 적용

위처럼 오류를 내보내는 대신, 참조하고 있는 곳을 찾아가서 자동으로 변경 또는 삭제되도록 해보자.

명령어

ALTER TABLE  `emp` DROP FOREIGN KEY  `emp_ibfk_1` ;
ALTER TABLE  `emp` ADD FOREIGN KEY (  `dept_no` ) REFERENCES  `test`.`dept` (
`dept_no`
) ON DELETE CASCADE ON UPDATE CASCADE;

→ 기존의 제약조건(RESTRICT) 제거

→ 새로운 제약조건 CASCADE 적용

실행 예시

mysql> ALTER TABLE  `emp` DROP FOREIGN KEY  `emp_ibfk_1` ;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE  `emp` ADD FOREIGN KEY (  `dept_no` ) REFERENCES  `test`.`dept` (
    -> `dept_no`
    -> ) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

5 변경 시도 2차

생산부의 부서번호를 1003에서 1004로 바꿔보자.

mysql> UPDATE dept SET dept_no='1004' WHERE dept_no='1003';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

오류 없이 적용된다.

mysql> SELECT * FROM dept WHERE dept_no='1004';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
|    1004 | 생산부    | 
+---------+-----------+

mysql> SELECT * FROM emp WHERE dept_no='1004';
+---------+---------+----------+
| emp_no  | dept_no | emp_name |
+---------+---------+----------+
| 2012002 |    1004 | 두시기   | 
| 2012003 |    1004 | 석삼     | 
+---------+---------+----------+

부서(dept) 테이블이 변경되었고, 그것을 참조하는 직원(emp) 테이블도 변경되었다.

mysql> DELETE FROM dept WHERE dept_no='1004';
Query OK, 1 row affected (0.00 sec)

부서번호 1004를 삭제.

mysql> SELECT * FROM dept;
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
|    1001 | 인사부    | 
|    1002 | 영업부    | 
+---------+-----------+

mysql> SELECT * FROM emp;  
+---------+---------+----------+
| emp_no  | dept_no | emp_name |
+---------+---------+----------+
| 2012001 |    1001 | 한놈     | 
+---------+---------+----------+

부서 테이블에서 생산부(1004)가 삭제되었고, 직원 테이블에서 생산부에 속해 있던 두시기, 석삼도 삭제되었다.

6 같이 보기