근원e♡수진♡라라 일상생활

MySQL Foreign key

외래 키는 데이터 베이스에서 참조 무결성을 보장하기 위해 사용이 됩니다. 참조 무결성이란 집합(Table)안에 1개의 속성(Column)이 다른 속성을 참조하는 관계에서 한쪽의 삭제 또는 업데이트로 인해 참조가 불가능 한 경우를 배재하기 위해 사용이 됩니다.

쉽게 예를 들어 학과 테이블과 이를 참조하는 학생 테이블이 있다고 하면 학과 테이블에 행이 삭제 되었다면 학생테이블에 기록된 학생의 정보는 학과가 없는 상태가 됩니다. 이를 위해 외래 키는 학제 되었을때  학생 테이블에 참조 하는 속성을 변경하여 참조무결성을 유지 시키는 기능을 합니다.

그 기능 은 아래와 같습니다. (CCL: 위키백과-동일조건변경 허락 3.0:외래키)
CASCADE
참조되는 측 관계 변수의 행이 삭제 되었을 경우에는 참조하는 측 관계 병수와 대응되는 모든 행들이 삭제 됩니다 . 참조되는 측 관계 변수의 행이 갱신 되었을 경우에는 참조하는 측 관계 변수의 외래 키 값은 같은 값으로 갱신됩니다.

RESTRICT
참조하는 측 관계 변수의 행이 남아 있는 경우에는 참조되는 측의행을 갱신하거나 삭제 할 수 없습니다. 이 경우에는 데이터 변경이 이루어 지지 않습니다.

NO ACTION
참조되는 측 관계변수에 대해 UPDATE, DELETE 가 실행됩니다. DBMS에서 SQL문장의 실행 종료시에 참조 정합성을 만족하는지 검사합니다. RESTRICT와 차이점은 트리거 또는 SQL문장의 시멘틱스 자체가 외래키의 제약을 채울것이라는 데에 있습니다. 이때는 SQL 문장 실행이 성공합니다. 외래 키의 제약이 만족되지 않은 경우에는 SQL문장이 실패한다.

SET NULL
참조되는 측 관계 변수에 대해 행이 갱신 또는 삭제 되었을 경우 , 참조하는 측 관계 변수의 행에 대한 외래키 값은 NULL로 설정이 됩니다. 이 옵션은 참조하는 측 관계 변수의 외래 키에 NULL 을 설정할 수 있는 경우에만 가능합니다. NULL 의 시멘틱스에 의해 참조하는 측 관계 변수에 대해 NULL이 있는 행은 , 참조 되는 측 관계 변수의 행을 필요로 하지 않습니다.

SET DEFAULT
SET NULL 과 비슷하지만 참조되는 측 관계 변수의 행이 갱신 또는 삭제 되었을 경우 참조하는 측 관계 변수의 외래키 값은 속성의 기본값으로 설정됩니다.

위와 같은 5개의 참조 조작이 SQL:2003에 규정되어 있습니다.

MySQL 에서는 InnoDB 에서 지원을 합니다. 지원하는 참조 조작은 RESTRICT, CASCADE, SET NULL, NO ACTION 을 지원합니다.

외래키 (Foreign key ) 사용 제약에 대해서 알아 보겠습니다.
1. 테이블 엔진은 InnoDB 를 사용해야 합니다.
2. 외래키로 설정할 두 테이블의 필드는 같은 데이터 형을 가지고 있어야 합니다.
3. 외래키를 설정할 필드는 인덱스 설정이 되어 있어야 합니다.
4. 외래키 설정이 불가능한 데이터 형은 BLOB, TEXT 형입니다.
5. 외래키 설정을 할때 CONSTRAINT 는 데이터 베이스 내에서 유일 해야 합니다. (지정하지 않으면 자동으로 지정)

실제 예를 알아보도록 하겠습니다.

CREATE TABLE tDepartment
(
	seq TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
	title VARCHAR(16) NOT NULL,
	PRIMARY KEY(`seq`)
)
ENGINE=InnoDB;

CREATE TABLE tStudent
(
seq SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
deptSeq TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
name VARCHAR(12),
PRIMARY KEY (`seq`),
INDEX idx_deptSeq(`deptSeq`),
CONSTRAINT ikf_deptSeq FOREIGN KEY (`deptSeq`)
REFERENCES tDepartment (`seq`)
	ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB;

위와 같이 테이블을 생성합니다.

INSERT INTO tDepartment (`seq`, `title`) VALUES 
(1, '경제학과'), (2, '정보통신학과'),
(3, '수학과'), (4, '경영학과'), (5, '컴퓨터공학과');

INSERT INTO tStudent (`seq`, `deptSeq`, `name`) VALUES 
(1, 1, '홍길동'),(2, 2, '성춘향'),
(3, 3, '고길동'),(4, 4, '또치'),
(5, 5, '변사또');

위와 같이 값을 넣어 보겠습니다.

위와 같이 참조하는 테이블의 값이 변경이 되면 참조 하고 있는 테이블의 값도 설정한 형식에 따라 변경이 됩니다. 참조 무결성을 유지 하기 위해서 외래키를 설정하는 것은 매우 유용하게 사용될 수 있고 편리하게 자료를 관리 할 수 있습니다.