- MySQL 외래키 옵션 변경
1 예제[ | ]
- MySQL 외래키 생성의 예제를 그대로 활용한다.
Console
Copy
mysql> select * from dept;
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| 1001 | 인사부 |
| 1002 | 영업부 |
| 1003 | 생산부 |
+---------+-----------+
Console
Copy
mysql> select * from emp;
+---------+---------+----------+
| emp_no | dept_no | emp_name |
+---------+---------+----------+
| 2012001 | 1001 | 한놈 |
| 2012002 | 1003 | 두시기 |
| 2012003 | 1003 | 석삼 |
+---------+---------+----------+
2 제약조건 확인[ | ]
MySQL 제약조건 확인 문서를 참고하십시오.
Console
Copy
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 - 갱신시 제약
참조되고 있는 값을 삭제 또는 변경하려고 하면, 자료의 정합성이 깨지게 되므로 다음과 같이 오류가 발생된다.
Console
Copy
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`))
Console
Copy
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 적용[ | ]
위처럼 오류를 내보내는 대신, 참조하고 있는 곳을 찾아가서 자동으로 변경 또는 삭제되도록 해보자.
- 명령어
sql
Copy
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 적용
- 실행 예시
Console
Copy
mysql> ALTER TABLE `emp` DROP FOREIGN KEY `emp_ibfk_1` ;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
Console
Copy
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로 바꿔보자.
Console
Copy
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
오류 없이 적용된다.
Console
Copy
mysql> SELECT * FROM dept WHERE dept_no='1004';
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| 1004 | 생산부 |
+---------+-----------+
Console
Copy
mysql> SELECT * FROM emp WHERE dept_no='1004';
+---------+---------+----------+
| emp_no | dept_no | emp_name |
+---------+---------+----------+
| 2012002 | 1004 | 두시기 |
| 2012003 | 1004 | 석삼 |
+---------+---------+----------+
부서(dept) 테이블이 변경되었고, 그것을 참조하는 직원(emp) 테이블도 변경되었다.
Console
Copy
mysql> DELETE FROM dept WHERE dept_no='1004';
Query OK, 1 row affected (0.00 sec)
부서번호 1004를 삭제.
Console
Copy
mysql> SELECT * FROM dept;
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| 1001 | 인사부 |
| 1002 | 영업부 |
+---------+-----------+
Console
Copy
mysql> SELECT * FROM emp;
+---------+---------+----------+
| emp_no | dept_no | emp_name |
+---------+---------+----------+
| 2012001 | 1001 | 한놈 |
+---------+---------+----------+
부서 테이블에서 생산부(1004)가 삭제되었고, 직원 테이블에서 생산부에 속해 있던 두시기, 석삼도 삭제되었다.
6 같이 보기[ | ]
편집자 Jmnote Jmnote bot
로그인하시면 댓글을 쓸 수 있습니다.
문태부 2017-03-29
- 분류 댓글:
- MySQL (5)
CentOS MySQL 설치 ― …MySQL BigDump ― JmnoteMySQL STRICT TRANS TABLES ― John JeongMySQL 외래키 옵션 변경 ― 문태부MySQL 컬럼명으로 테이블 찾기 ― Stly3466