[MYSQL][06] 외래키(Foreign Key, FK)의 참조 무결성

외래키(Foreign Key, FK)의 참조 무결성

외래키가 기본키를 참조할 때 참조무결성이 발생 가능한 상황

1. 부모 테이블 - 데이터 삽입 : (X) 값이 생겨도 무관하다

2. 부모 테이블 - 데이터 삭제 : (O) 참조 못할 수 있음

3. 부모 테이블 - 데이터 수정 : (O) 참조 못할 수 있음

4. 자식 테이블 - 데이터 삽입 : (O) 잘못된 값 삽입

5. 자식 테이블 - 데이터 삭제 : (X) 참조할 테이블에 문제가 없기 때문에 가능성이 없다

6. 자식 테이블 - 데이터 수정 : (O) 외래키가 수정되면 참조 무결성 발생

※ 부모테이블 데이터 삽입, 자식테이블 데이터 삭제시 참조 무결성이 발생하지 않는다.

부모, 자식 테이블 생성

부모테이블에 PK 설정 자식테이블에 부모테이블의 PK를 참조하는 FK 설정

mysql> create table parent_table(col1 int primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> create table child_table(col1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> alter table child_table add foreign key (col1) references parent_table(col1);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

부모 PK 데이터 INSERT

mysql> insert into parent_table value (10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into parent_table value (20);
Query OK, 1 row affected (0.00 sec)

mysql> insert into parent_table value (30);
Query OK, 1 row affected (0.00 sec)

mysql> insert into parent_table value (40);
Query OK, 1 row affected (0.00 sec)

mysql> insert into parent_table value (50);
Query OK, 1 row affected (0.00 sec)

mysql> select * from parent_table;
+------+
| col1 |
+------+
|   10 |
|   20 |
|   30 |
|   40 |
|   50 |
+------+
5 rows in set (0.00 sec)

자식 테이블 외래키가 지정된(부모 PK 참조) 필드에 부모 PK가 없는 값 입력

mysql> insert into child_table value (100);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mysite`.`child_table`, CONSTRAINT `child_table_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `parent_table` (`col1`))

부모테이블에 있는 값 입력

mysql> insert into child_table value (10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into child_table value (20);
Query OK, 1 row affected (0.00 sec)

mysql> insert into child_table value (30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from child_table;
+------+
| col1 |
+------+
|   10 |
|   20 |
|   30 |
+------+
3 rows in set (0.00 sec)

부모 테이블 데이터 삭제 시 오류

자식 테이블 데이터를 먼저 삭제하고 부모 데이터 삭제한다.

mysql> delete from parent_table where col1 = 10;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mysite`.`child_table`, CONSTRAINT `child_table_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `parent_table` (`col1`))

자식 삭제 후 부모 데이터 삭제

테이블도 자식, 부모 순으로 삭제해야한다

mysql> delete from child_table where col1 = 10;
Query OK, 1 row affected (0.00 sec)

mysql> delete from parent_table where col1 = 10;
Query OK, 1 row affected (0.04 sec)

mysql> select * from parent_table;
+------+
| col1 |
+------+
|   20 |
|   30 |
|   40 |
|   50 |
+------+
4 rows in set (0.00 sec)

mysql> select * from child_table;
+------+
| col1 |
+------+
|   20 |
|   30 |
+------+
2 rows in set (0.00 sec)

자식테이블 제약조건 확인

mysql> show create table child_table;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| child_table | CREATE TABLE `child_table` (
  `col1` int DEFAULT NULL,
  KEY `col1` (`col1`),
  CONSTRAINT `child_table_ibfk_1` FOREIGN KEY (`col1`) REFERENCES `parent_table` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

제약조건 제거

ALTER TABLE child_table DROP CONSTRAINT child_table_ibkf_1;

mysql> alter table child_table drop constraint child_table_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

FOREIGN KEY 비활성화

운영중인 서비스 및 타 테이블에 영향을 줄 수 있기 때문에 알아만 두고 넘어가자.

SELECT @@FOREIGN_KEY_CHECK;

비활성화

SET FOREIGN_KEY_CHECK = 0;

활성화

SET FOREIGN_KEY_CHECK = 1;