커뮤니티 정보

제목 MySQL Foreign Key Using
등록자 admin 등록일 2017.01.26 01:04:21 검색 1844

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) - Supported by 플래쉬카페(yourgirlis) -

... 자료없음 ...