test.sql
test.sql[MySQL] Foreign Key 설정 방법 및 옵션 설명
※ MyISAM엔진에서는 지원하지 않고, InnoDB에서 지원합니다.
사용서버버젼 : Server version: 5.1.41-community MySQL Community Server (GPL)
Create구문에 Foreign Key를 넣어서 실행한 후 show create table 테이블명; 명령을 주면
Foreign Key 구문이 빠져 있다.
1. Foreign Key(외래키)는 DB 에서 참조 무결성을 보장하기 위해 사용된다.
2. Foreign Key 를 가지는 부모테이블과 자식 테이블은 Engine 이 InnoDB 여야만 한다.
3. Foreign Key 로 설정할 두 테이블의 필드는 같은 데이터 형을 가지고 있어야 한다.
4. Foreign Key 로 설정하여 참조할수 있는 값은 꼭 Primary Key 가 아니여도, Unique(인덱스) 이면
가능하다.
5. Foreign Key 로 설정이 불가능한 데이터 형은 BLOB,TEXT 형이다.
6. Foreign Key 를 설정 할때 Constraint 는 DB 내에서 유일해야한다. (지정하지 않으면 자동으로 지정됨)
7. Foreign Key는 테이블 생성시 설정 또는 생성 후에 ALTER 명령어로 설정할 수 있다.
sql 문 : ALTER TABLE 자식 테이블명
ADD FOREIGN KEY (자식 테이블의 컬럼명) REFERENCES 부모 테이블명(부모 테이블의 컬럼명)
on UPDATE 옵션 on DELETE 옵션;
8. Foreign key 옵션
* 여기서 햇갈리지 말아야 할 것,!
만약 A 테이블이 부모 테이블이고 B 테이블이 자식 테이블이라 가정하고, FK에 해당하는 필드가 A_NO와 B_NO 라
할 때, A_NO와 B_NO의 값이 같은 데이터들에 대하여 아래에서 말하는것을 잊지말자.
1) on Delete
Cascade : 부모 데이터 삭제시 자식 데이터도 동시 삭제.
Set null : 부모 데이터 삭제시 해당되는 자식 데이터의 Columm은 Null 로 처리.
Set default : 부모 데이터 삭제시 자식 데이터의 Columm은 기본 값(Default) 으로 Update.
Restrict : 자식 테이블에 데이터가 남아 있는 경우 부모 테이블의 데이터는 삭제 불가.
No Action :
2) on Update
Cascade : 부모 데이터 수정시 자식 데이터도 동시 수정.
Set null : 부모 데이터 수정시 해당되는 자식 데이터의 Columm은 Null 로 처리.
Set default : 부모 데이터 수정시 자식 데이터의 Columm은 기본 값(Default) 으로 Update.
Restrict : 자식 테이블에 데이터가 남아 있는 경우 부모 테이블의 데이터는 수정 불가.
No Action :
cascade : Automatically Delete or Update
set null : The foreign key table is set NULL
restrict : Rejects the delete or udpate operation
no action : the same as restrict
When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified using ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. MySQL supports five options regarding the action to be taken, listed here:
-
CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. BothON DELETE CASCADEandON UPDATE CASCADEare supported. Between two tables, do not define severalON UPDATE CASCADEclauses that act on the same column in the parent table or in the child table.NoteCascaded foreign key actions do not activate triggers.
-
SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table toNULL. BothON DELETE SET NULLandON UPDATE SET NULLclauses are supported.If you specify a
SET NULLaction, make sure that you have not declared the columns in the child table asNOT NULL. -
RESTRICT: Rejects the delete or update operation for the parent table. SpecifyingRESTRICT(orNO ACTION) is the same as omitting theON DELETEorON UPDATEclause. -
NO ACTION: A keyword from standard SQL. In MySQL, equivalent toRESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, andNO ACTIONis a deferred check. In MySQL, foreign key constraints are checked immediately, soNO ACTIONis the same asRESTRICT. -
SET DEFAULT: This action is recognized by the MySQL parser, but bothInnoDBandNDBreject table definitions containingON DELETE SET DEFAULTorON UPDATE SET DEFAULTclauses.
For an ON DELETE or ON UPDATE that is not specified, the default action is always RESTRICT.
NO ACTION
참조되는 측 관계변수에 대해 UPDATE, DELETE 가 실행됩니다. DBMS에서 SQL문장의 실행 종료시에 참조 정합성을 만족하는지 검사합니다. RESTRICT와 차이점은 트리거 또는 SQL문장의 시멘틱스 자체가 외래키의 제약을 채울것이라는 데에 있습니다. 이때는 SQL 문장 실행이 성공합니다. 외래 키의 제약이 만족되지 않은 경우에는 SQL문장이 실패한다.
* on Delete 나 on Update 가 지정되어 있지 않으면 NO ACTION 이 기본 값으로 설정된다.
* MySQL 에서는 Set Default 를 지원하지 않는다.
9. 그 밖의 참고 명령문
1) test 테이블의 외래 키 제약을 나열시키는 명령문 :
SHOW TABLE STATUS FROM LIKE 'test';
2) 테이블 생성 구문을 보기위한 명령문
Show create table 테이블명;
* 참조 무결성이란?
자식테이블 내에 1개의 필드(Columm)가 부모 테이블의 필드를 참조하는 관계에서
부모 쪽 데이터의 삭제 또는 업데이트로 인해 참조가 가능한지 불가능한지의 관계.