devcken.io

Thoughts, stories and ideas.

Foreign Key

MySQL 오류 코드 1215 다루기

Dealing With MySQL Error Code 1215: "Cannot Add Foreign Key Constraint"

이 포스트는 Dealing With MySQL Error Code 1215: "Cannot Add Foreign Key Constraint"를 번역한 글입니다. 심각한 오역이나 손발이 오그라드는 직역이 있을 수도 있습니다.

오래된 에러 1215는 굉장히 흔하지만, MySQL은 진짜 원인을 알려주지 않습니다. 다행히도, 문법적으로 파헤쳐볼 12가지 방법이 존재합니다.

이 블로그에서, MySQL 에러 코드 1215: "외래키 제한사항을 추가할 수 없습니다"를 해결할 방법을 알아볼 것입니다.

우리의 고객은 종종 "제 데이터베이스가 오류 1215로 실패해요", "외래키를 만들려고 하는데 자꾸 실패해요" 또는 "왜 제한사항을 만들 수 없는 걸까요?" 와 같은 이야기를 해옵니다. 솔직히, 저 오류 메시지는 그리 도움이 되지 않습니다. 그냥 다음과 같은 오류를 보여줄 뿐이죠.

ERROR 1215 (HY000): Cannot add foreign key constraint  

그러나 MySQL은 결코 그것이 실패했는지 알려주지 않습니다. 실제로는 그것이 일어날 수많은 원인이 존재합니다. 이 블로그 포스트는 오류 1215가 일어나는 가장 흔한 이유, 여러분의 경우에 많는 경우를 찾기 위한 진단 그리고 왜래 키 추가를 위한 가능한 방법에 대해 설명합니다.

(참고: 여기서 제안한 해결 방법들을 적용할 때는 주의하시기 바랍니다. 많은 경우에 상위 테이블의 ALTER를 필요로 하고, 그로 인해 테이블 크기, MySQL 버전 그리고 적용된 특정 ALTER 연산에 따라 테이블을 오랜 시간 차단할 수 있습니다. 많은 경우에, pt-online-schema-change를 사용하는 것이 좋습니다)

1) 제한사항이 참조하는 테이블 혹은 인덱스가 아직 존재하지 않는 경우(보통 덤프를 로드하는 경우)

진단: 각 상위 테이블에 대해 SHOW TABLES 혹은 SHOW CREATE TABLE을 실행하세요. 어느 것이든 1146 오류가 난다면, 잘못된 순서로 생성하고 있다는 것을 의미합니다.

해결: 누락된 create table을 실행하고 나서 다시 시도해보거나 foreign-key-checks를 잠시 비활성화하세요. 특히 순환 참조가 존재할 수도 있는 데이터베이스를 백업으로 복구하는 동안 필요합니다. 다음처럼 실행하세요:

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;  
SET FOREIGN_KEY_CHECKS=0;  
SOURCE /backups/mydump.sql; -- restore your backup within THIS session  
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;  

예제:

mysql> CREATE TABLE child (  
    ->   id INT(10) NOT NULL PRIMARY KEY,
    ->   parent_id INT(10),
    ->   FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)
    -> ) ENGINE INNODB;
ERROR 1215 (HY000): Cannot add foreign key constraint  
# We check for the parent table and is not there.
mysql> SHOW TABLES LIKE 'par%';  
Empty set (0.00 sec)  
# We go ahead and create the parent table (we’ll use the same parent table structure for all other example in this blogpost):
mysql> CREATE TABLE parent (  
    ->   id INT(10) NOT NULL PRIMARY KEY,
    ->   column_1 INT(10) NOT NULL,
    ->   column_2 INT(10) NOT NULL,
    ->   column_3 INT(10) NOT NULL,
    ->   column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
    ->   KEY column_2_column_3_idx (column_2, column_3),
    ->   KEY column_4_idx (column_4)
    -> ) ENGINE INNODB;
Query OK, 0 rows affected (0.00 sec)  
# And now we re-attempt to create the child table
mysql> CREATE TABLE child (  
    ->   id INT(10) NOT NULL PRIMARY KEY,drop table child;
    ->   parent_id INT(10),
    ->   FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)
    -> ) ENGINE INNODB;
Query OK, 0 rows affected (0.01 sec)  

제한사항 참조 내 테이블 혹은 인덱스에 대해 따옴표를 잘못 사용하는 경우

진단: 각각의 FOREIGN KEY 선언을 살펴보고 객체 수식어 앞뒤로 따옴표가 없는건 아닌지 확인하세요. 혹은 테이블 앞뒤 따옴표와 컬럼 앞뒤로 따옴표가 구별되서 존재하는지 확인하시기 바랍니다.

해결: 따옴표를 아예 두지 마세요. 또는 테이블 혹은 컬럼에 개별적으로 따옴표를 두시기 바랍니다.

예제:

# wrong; single pair of backticks wraps both table and column
ALTER TABLE child  ADD FOREIGN KEY (parent_id) REFERENCES `parent(id)`;  
# correct; one pair for each part
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent`(`id`);  
# also correct; no backticks anywhere
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);  
# also correct; backticks on either object (in case it’s a keyword)
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(`id`);  

3) 제한사항 내 로컬 키, 외래 테이블 혹은 컬럼에 오타가 있는 경우

진단: SHOW TABLESSHOW COLUMNS를 실행해서 REFERENCES 선언과 비교하세요.

해결: 오타가 있다면 수정하세요.

예제:

# wrong; Parent table name is ‘parent’
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES pariente(id);  
# correct
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);  

4) 제한사항이 참조하는 컬럼이 외래 컬럼과 동일한 타입 혹은 길이를 갖지 않는 경우

진단: `SHOW CREATE TABLE parent를 사용해 로컬 컬럼과 참조되는 컬럼 모두 동일한 데이터 타입과 길이를 갖고 있는지 확인하세요.

해결: 하위 테이블 내 컬럼 정의가 상위 테이블의 정의와 일치하도록 DDL 문을 수정하세요.

예제:

FOREIGN KEY

5) 왜래 객체가 어떤 종류의 키도 아닌 경우

진단: SHOW CREATE TABLE parent를 사용해 REFERENCES 부분이 한 컬럼을 가리키고 있고 그 컬럼이 어떤 식으로든 인덱싱되지 않았는지를 확인합니다.

해결: 상위 테이블의 컬럼을 KEY, UNIQUE KEY 혹은 PRIMARY KEY로 만듭니다.

예제:

# wrong; single pair of backticks wraps both table and column
ALTER TABLE child  ADD FOREIGN KEY (parent_id) REFERENCES `parent(id)`;  
# correct; one pair for each part
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent`(`id`);  
# also correct; no backticks anywhere
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);  
# also correct; backticks on either object (in case it’s a keyword)
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(`id`);  

6) 참조된 컬럼이 가장 왼쪽에 있지 않을 때, 외래 키가 멀티 컬럼의 PK 혹은 UK인 경우

진단: SHOW CREATE TABLE parent를 실행해 REFERENCES 부분이 일부 멀티 컬럼 인덱스 내 컬럼을 가리키고 있을 때 그것의 정의가 가장 왼쪽에 있지 않은지 확인하세요.

해결: 참조 컬럼이 가장 왼쪽의 (혹은 가장 왼쪽에만 있는) 컬럼인 상위 테이블에 인덱스를 추가하세요.

예제:

SHOW TABLES

7) 두 테이블/컬럼 간에 캐릭터셋/콜레이션이 다른 경우

진단: SHOW CREATE TABLE parent를 실행하고 하위 컬럼(그리고 테이블)의CHARACTER SETCOLLATE 부분이 상위 테이블의 것과 일치하는지를 비교하세요.

해결: 하위 테이블의 DDL을 수정해 상위 테이블 및 컬럼의 캐릭터셋과 콜레이션에 일치시키도록 합니다(또는 하위 테이블이 요구하는 정의대로 상위 테이블을 ALTER합니다).

예제:

SHOW COLUMNS

8) 상위 테이블이 InnoDB를 사용하고 있지 않은 경우

진단: SHOW CREATE TABLE parent를 실행하고 ENGINE=INNODB인지 아닌지를 확인합니다.

해결: 상위 테이블을 ALTER하여 엔진을 InnoDB로 변경하세요.

예제:

REFERENCES

9) 외래 키 참조를 위해 문법 숏핸드를 사용하는 경우

진단: REFERENCE 부분에 테이블 이름만 들어가 있는지를 확인하세요. 이전 동료였던 Bill Karwin이 http://stackoverflow.com/questions/41045234/mysql-error-1215-cannot-add-foreign-key-constraint에서 언급한대로, MySQL은 숏핸드를 지원하지 않습니다(그것이 유효한 SQL일지라도).

해결: 하위 테이블 DDL을 수정해 테이블과 컬럼 모두 지정하도록 합니다.

예제:

# wrong; Parent table name is ‘parent’
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES pariente(id);  
# correct
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);  

10) 상위 테이블이 파티셔닝된 경우

진단: SHOW CREATE TABLE parent를 실행해 테이블이 파티셔닝됐는지 아닌지를 확인합니다.

해결: 파티셔닝 제거(즉, 모든 파티션을 단일 테이블로 합치는 것) 만이 유일한 해결책입니다.

예제:

`SHOW CREATE TABLE parent

11) 참조 컬럼이 생성된 가상 컬럼인 경우(이것은 5.7 이상의 버전에서만 가능합니다)

진단: SHOW CREATE TABLE parent를 실행하고 참조 컬럼이 가상 컬럼이 아닌지 확인합니다.

해결: 상위 테이블을 CREATE 혹은 ALTER하여 컬럼을 생성이 아니라 저장되도록 만듭니다.

예제:

SHOW CREATE TABLE parent

12) 제한사항 액션을 위해 SET DEFAULT를 사용하는 경우

진단: 하위 테이블 DDL을 확인하여 제한사항 액션(ON DELETE, ON UPDATE)이 SET DEFAULT를 사용하는지 봅니다.

해결: 하위 테이블의 CREATE 혹은 ALTER 구문에서 SET DEFAULT를 사용하는 액션을 제거하거나 수정합니다.

예제:

# wrong; this parent table has a generated virtual column
CREATE TABLE parent (  
  id INT(10) NOT NULL PRIMARY KEY,
  column_1 INT(10) NOT NULL,
  column_2 INT(10) NOT NULL,
  column_virt INT(10) AS (column_1 + column_2) NOT NULL,
  KEY column_virt_idx (column_virt)
) ENGINE INNODB;
# correct: make the column STORED so it can be used as a foreign key
ALTER TABLE parent DROP COLUMN column_virt, ADD COLUMN column_virt INT(10) AS (column_1 + column_2) STORED NOT NULL;  
# And now the child table can be created pointing to column_virt
CREATE TABLE child (  
  id INT(10) NOT NULL PRIMARY KEY,
  parent_virt INT(10) NOT NULL,
  FOREIGN KEY (parent_virt) REFERENCES parent(column_virt)
) ENGINE INNODB;

여기 나오는 해결책들이 여러분이 원하는 것이 아닐지도 모른다는 것을 알고 있지만, MySQL의 한계는 당분간 애플리케이션 측에서 극복해야 합니다. 8.0이 출시되면 위 목록이 더 짧아지기를 바랍니다!

MySQL이 에러 1215로 실패할 수 있는 다른 원인을 알고 있다면, 코멘트를 통해 알려주시기 바랍니다.

외래키 제약과 관련된 좀 더 많은 정보는 다음 링크에서 볼 수 있습니다: https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html