MySQL 외래키 옵션 변경

MySQL 외래키 옵션 변경

1 예제[ | ]

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 제약조건 확인[ | ]

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 같이 보기[ | ]

편집자 J Jmnote Jmnote bot
문서 댓글 (1)
문태부 2017-03-29
진짜 필요한 거였는데 이런걸 이제야 알다니 정말 감사합니다.
  • CentOS MySQL 설치
    RHEL5.x 버전에서 MySQL-5.5.45 버전 설치시, /usr/bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data 와 같이 설정해 주지 않으면 정상 동작하지 않는 경우가 있었습니다. 확인 바랍니다.
  • MySQL BigDump
    'MySQL server has gone away'는 백업본 전체의 크기가 크다기 보다는, 특정 쿼리문 1개의 크기가 너무 커서 처리를 못하는 거라서, 이런 도구로 해결하기는 어려울 것 같습니다.J Jmnote
  • MySQL STRICT TRANS TABLES
    @Jmnote 감사합니다.~ John Jeong
  • MySQL 외래키 옵션 변경
    진짜 필요한 거였는데 이런걸 이제야 알다니 정말 감사합니다. 문태부
  • MySQL 컬럼명으로 테이블 찾기
    회사 가서 쓰기 좋은 코드 1추 Stly3466