MySQL은 3.23.43b버전부터 InnoDB를 이용한 foreign key지원을 시작하게 된다.
따라서, 테이블 생성시 type=innodb를 추가해야 한다.(버클리DB등은 지금 고려하지 않겠다.)
foreign key를 지원한다는 의미는 데이터의 무결성(Integrity)를 보장한다는 의미이다.
문법:
[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
REFERENCES table_name (index_col_name, ...)
[ON DELETE CASCADE | ON DELETE SET NULL]
foreign key는 다음 사항을 만족해야 생성될 수 있다.
1. foreign key에는 인덱스가 있어야 한다.(수동으로 명시적 생성이 요구된다)
2. 컬럼이 숫자형이라면, primary key와 크기,signedness(양수/음수 여부)가 동일해야 하고, 문자형이때는 크기까지 동일할 필요는 없다.
ON DELETE CASCADE : 부모테이블의 데이터가 삭제되면, 이와 연관된 자식테이블의 데이터도 함께 삭제
ON DELETE SET NULL: 부모테이블의 데이터가 삭제되면, 이와 연관된 자식테이블의 데이터의 해당 컬럼값을 null로 만듬.
생성예)
mysql> create table parent
-> (
-> id int not null,
-> primary key(id)
-> ) type=innodb;
Query OK, 0 rows affected (0.01 sec)
foreign key컬럼에 인덱스를 생성한 것과, 테이블type을 innodb로 했음을 파악하라.
부가적으로, on delete set null을 사용하여 부모테이블에서 delete가 발생하면 해당되는 데이터를 null변경되게 했다.
mysql> create table child
-> (
-> id int,
-> parent_id int,
-> index par_ind(parent_id),
-> foreign key(parent_id) references parent(id) on delete set null
-> ) type=innodb;
Query OK, 0 rows affected (0.03 sec)
테스트)
mysql> insert into parent values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into child values(1,1);
Query OK, 1 row affected (0.00 sec)
현재 부모테이블(parent)와 자식테이블(child)의 데이터는 다음과 같다.
mysql> select * from parent;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.02 sec)
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 1 |
+------+-----------+
1 row in set (0.00 sec)
자식테이블(child)에 중복되는 키의 insert는 당연히 참조무결성에러를 발생시킨다.
mysql> insert into child values(1,2);
ERROR 1216: Cannot add a child row: a foreign key constraint fails
위에서 on delete set null 옵션을 주었으므로 확인해보자.
부모테이블의 데이터를 삭제하면 해당되는 자식테이블의 데이터는 null로 변경된다.
mysql> delete from parent where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | NULL |
+------+-----------+
1 row in set (0.00 sec)
문제점)
다시 부모테이블을 생성하고 동일한 데이터를 넣자.
child를 가진 parent가 아무런 에러없이 삭제되어버린다.(foreign key constraints가 깨져버리는 것이다)
mysql> drop table parent;
Query OK, 0 rows affected (0.00 sec)
다시 parent테이블을 생성하면 어떻게 될까
mysql> create table parent
-> (
-> id int not null,
-> primary key(id)
-> ) type=innodb;
Query OK, 0 rows affected (0.00 sec)
참조무결성 제약조건으로 인해 입력되지 않는다.
mysql> insert into child values(5,1);
ERROR 1216: Cannot add a child row: a foreign key constraint fails
하지만 parent에 다시 데이터를 insert해주면 입력됨을 알 수 있다.
왠지 어설픈 구석이 없지 않다. MySQL의 차기버전에서는 좀더 세련된 체크루틴을 구현하면 어떨까 싶다.
mysql> insert into parent values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into child values(5,1);
Query OK, 1 row affected (0.00 sec)
parent데이터의 삭제로 인해 null로 변경된 컬럼은 그대로 null로 남아있음도 확인하자.
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | NULL |
| 5 | 1 |
+------+-----------+
2 rows in set (0.00 sec)
mysql> |
This article comes from dbakorea.pe.kr (Leave this line as is)
|
