테이블 변경 (alter) 을 진행할 때, 다음과 같은 에러가 리턴되는 경우가 있다.
ERROR 1025 (HY000): Error on rename of './test/#sql-2fa8_1' to './test/test2' (errno: 150)
이 경우는, alter 하는 테이블에 fk constraint 가 존재할 경우 발생할 수 있다.
이러한 경우에 대해 테스트를 해본다.
PARENT_TBL 이 부모 테이블, CHILD_TBL 이 자식 테이블로, PARENT_TBL 의 id 를 참조하는 테이블이다.
CREATE TABLE `PARENT_TBL` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
CREATE TABLE `CHILD_TBL` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`cvalue` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_parent_tbl_id` (`pid`),
CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ;
// 데이터 입력
insert into PARENT_TBL (value) values ('jm732') ;
insert into PARENT_TBL (value) values ('jm732') ;
insert into PARENT_TBL (value) values ('jm732') ;
insert into PARENT_TBL (value) values ('jm732') ;
insert into PARENT_TBL (value) values ('jm732') ;
insert into PARENT_TBL (value) values ('jm732') ;
+----+-------+
| id | value |
+----+-------+
| 1 | jm732 |
| 2 | jm732 |
| 3 | jm732 |
| 4 | jm732 |
| 5 | jm732 |
| 6 | jm732 |
+----+-------+
// 데이터 입력
INSERT INTO CHILD_TBL (pid, cvalue) values (1, 'child_jm732') ;
INSERT INTO CHILD_TBL (pid, cvalue) values (1, 'child_jm732') ;
INSERT INTO CHILD_TBL (pid, cvalue) values (1, 'child_jm732') ;
+----+-----+-------------+
| id | pid | cvalue |
+----+-----+-------------+
| 2 | 1 | child_jm732 |
| 3 | 1 | child_jm732 |
| 4 | 1 | child_jm732 |
+----+-----+-------------+
// PARENT_TBL 에 없는 값을 pid 로 지정하면 에러 발생한다.
INSERT INTO CHILD_TBL (pid, cvalue) values (7, 'child_jm732') ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`CHILD_TBL`, CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
이러한 구성에서 PARENT_TBL 에 id 컬럼 타입을 변경하는 alter 를 수행해보자.
mysql> alter table PARENT_TBL change id id bigint NOT NULL AUTO_INCREMENT ;
ERROR 1025 (HY000): Error on rename of './test/#sql-72f5_330c7' to './test/PARENT_TBL' (errno: 150)
에러가 발생한다.
이러한 경우에는
child 테이블의 fk constraint 를 임시 삭제하고 parent 테이블에 alter 를 수행해야 한다.
1. CHILD_TBL 에 FK 삭제
alter table CHILD_TBL drop foreign key fk_parent_tbl_id ;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
show create table CHILD_TBL \G
*************************** 1. row ***************************
Table: CHILD_TBL
Create Table: CREATE TABLE `CHILD_TBL` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`cvalue` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_parent_tbl_id` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
2. PARENT_TBL 에서 컬럼 변경
mysql> alter table PARENT_TBL change id id bigint NOT NULL AUTO_INCREMENT ;
Query OK, 6 rows affected (0.12 sec)
Records: 6 Duplicates: 0 Warnings: 0
3. CHILD_TBL 에서 참조하는 컬럼도 추가
alter table CHILD_TBL change pid pid bigint NOT NULL ;
Query OK, 3 rows affected (0.29 sec)
Records: 3 Duplicates: 0 Warnings: 0
//만일 참조하는 컬럼을 수정하지 않을 경우 , CONSTRAINT 를 다시 추가할 때 에러가 발생한다.
ERROR 1005 (HY000): Can't create table 'test.#sql-72f5_330c7' (errno: 150)
4. CHILD_TBL 에 FK 다시 추가
alter table CHILD_TBL ADD CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ;
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0
완료되면 다음과 같이 int -> bigint 로 변경된 스키마를 확인할 수 있다.
CREATE TABLE `PARENT_TBL` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`value` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
CREATE TABLE `CHILD_TBL` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pid` bigint(20) NOT NULL,
`cvalue` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_parent_tbl_id` (`pid`),
CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
자세한 에러를 확인하고자 한다면, 다음 명령을 확인할 수 있다.
show engine indoor status \G
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
150825 9:56:45 Error in foreign key constraint of table test/#sql-72f5_330c7:
FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
...