[MySQL] ERROR 1822 (HY000): Failed to add the foreign key constraint - 외래키 제약조건의 삭제 & 추가를 통한 외래키 변경 방법

들어가기에 앞서

본 게시물은 'MySQL 8.0' 버전을 이용한 '명령 프롬프트(cmd)' 환경에서 작성한 코드를 토대로 만들어졌습니다.

이번 게시물에서는 외래키 제약조건을 설정해주거나 외래키 설정을 바꿔줄 때 발생하는 오류를 해결하는 방법에 대해서 알아보겠습니다.

제거할 제약조건의 이름 확인

# 테이블명이 student인 테이블의 제약조건 목록을 확인
mysql> SELECT * FROM information_schema.table_constraints WHERE table_name='student';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | univ              | PRIMARY         | univ         | student    | PRIMARY KEY     | YES      |
| def                | univ              | student_ibfk_1  | univ         | student    | FOREIGN KEY     | YES      |
| def                | univ              | student_ibfk_2  | univ         | student    | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
3 rows in set (0.00 sec)

기존 외래키 제약조건을 제거해주기 위해서 우선 해당 테이블의 제약조건 목록을 확인해줍니다.

위의 결과에서 'CONSTRAINT_NAME' 속성에 해당하는 'PRIMARY'와 'student_ibfk_1'이 제약조건들의 이름입니다.

'CONSTRAINT_TYPE' 속성을 통해서 'PRIMARY'는 기본키 제약조건이고 'student_ibfk_1'는 외래키 제약조건임을 알 수 있습니다.

기존 외래키 제약조건 제거

# student 테이블에서 제약조건 이름이 student_ibfk_1인 제약조건을 제거
mysql> ALTER TABLE student DROP CONSTRAINT student_ibfk_1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# student 테이블에서 제약조건 이름이 student_ibfk_2인 외래키 제약조건을 제거
mysql> ALTER TABLE student DROP FOREIGN KEY student_ibfk_2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

제거하려는 제약조건의 이름을 알아냈으면 위의 두 명령어 중에 하나를 사용해서 해당 외래키 제약조건을 제거해주면 됩니다.

외래키가 참조할 테이블 생성 및 데이터 추가

mysql> CREATE TABLE professor(
    -> pfnum char(3), pfname varchar(20), dept varchar(20), email varchar(20),
    -> PRIMARY KEY(pfnum));
Query OK, 0 rows affected (0.11 sec)

외래키가 참조할 속성을 가진 새로운 테이블을 만들어줍니다.

저는 student 테이블의 major 속성이 professor 테이블의 dept 속성을 참조하도록 설계하였습니다.

mysql> INSERT INTO professor(pfnum, pfname, dept, email)
    -> VALUES('1001', 'Irene', 'english', 'irene@naver.com');
mysql> INSERT INTO professor(pfnum, pfname, dept, email)
    -> values('1002', 'Alice', 'mathematics', 'alice@naver.com');
mysql> INSERT INTO professor(pfnum, pfname, dept, email)
    -> values('1003', 'Dophamine', 'biology', 'dophamine@naver.com');
mysql> INSERT INTO professor(pfnum, pfname, dept, email)
    -> values('1004', 'Mong', 'engineering', 'minsu@naver.com');
mysql> INSERT INTO professor(pfnum, pfname, dept, email)
    -> values('1005', 'Einestein', 'physics', 'einestein@naver.com');
mysql> INSERT INTO professor(pfnum, pfname, dept, email)
    -> values('1006', 'Chogal', 'economics', 'chogal@naver.com.');

참조하려는 값들이 모두 존재하도록 만들어주기 위해서 생성한 테이블에 데이터들을 삽입해줍니다.

외래키 제약조건 추가 시도

# student 테이블의 major 속성을 professor 테이블의 dept 속성을 참조하는 외래키로 설정
mysql> ALTER TABLE student ADD FOREIGN KEY(major) REFERENCES professor(dept) [ON DELETE SET NULL / ON UPDATE CASCADE];
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'student_ibfk_1' in the referenced table 'professor'

# student 테이블의 major 속성을 professor 테이블의 dept 속성을 참조하는 외래키로 설정하는 제약조건 std_fk를 추가
mysql> ALTER TABLE student ADD CONSTRAINT std_fk FOREIGN KEY(major) REFERENCES professor(dept) [ON DELETE SET NULL / ON UPDATE CASCADE];
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'std_fk' in the referenced table 'professor'

이후에 새로운 제약조건을 추가해주려고 하면 오류가 발생합니다.

student 속성이 참조하려는 dept 속성에 대해서 참조 무결성이 위배되었기 때문입니다.

여기서 참조 무결성이란 외래키 값은 NULL이거나 참조 릴레이션의 기본키 값과 동일해야 함을 말합니다.

UNIQUE KEY & NOT NULL 설정

# professror 테이블의 dept 속성을 UNIQUE KEY로 설정
mysql> ALTER TABLE professor ADD UNIQUE KEY(dept);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

# professor 테이블의 dept 속성의 데이터 타입을 varchar(20)으로 변경 + NOT NULL 설정
mysql> ALTER TABLE professor MODIFY dept varchar(20) NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

해당 속성의 참조 무결성을 보장해주기 위해서 'UNIQUE KEY'와 'NOT NULL'을 설정해주도록 합니다.

기본키는 'UNIQUE KEY' 특성과 'NOT NULL' 특성을 모두 가지고 있기 때문에 오류가 발생하지 않습니다.

성공적으로 외래키 제약조건 추가

mysql> ALTER TABLE student ADD FOREIGN KEY(major) REFERENCES professor(dept) [ON DELETE SET NULL / ON UPDATE CASCADE];
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE student ADD CONSTRAINT std_fk FOREIGN KEY(major) REFERENCES professor(dept) [ON DELETE SET NULL / ON UPDATE CASCADE];
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

'외래키 제약조건 추가 시도'에서 사용한 명령어를 다시 한번 입력해주면 외래키가 성공적으로 설정되는것을 볼 수 있습니다.