FOREIGN KEY를 알아보자.

728x90

Abstract

foreign key에 대해 알아보자

Description

Foreign key는 다른 테이블의 기본키를 가리키기 위한 필드로 사용하는 키이며, 참조 무결성(Referential Integrity)를 확인하기 위하며 사용된다. 참조 무결성이란 필드를 참조하는 관계에서 부모 쪽의 데이터가 삭제 또는 업데이트를 하는 경우에 발생하는 무결성을 보장하기 위한 것을 말한다.

FOREIGN KEY에 관련된 제약사항

FOREIGN KEY를 사용하려면 몇 가지 사항을 지켜야 한다. 아래와 같은 부분들이 존재한다.

Foreign key를 가지는 테이블은 Engine이 InnoDB여야 한다.
Foreign key를 생성할 때는 참조할 테이블(parent)와 참조하는 테이블(child) 간의 자료형이 동일해야 한다. 예를 들어 parent의 기본키를 BigInt로 선언헀는데 child 테이블에 참조할 필드에 smallint로 걸면 정의되지 않는다.

CREATE TABLE parent (
	reference_id BIGINT(1) AUTO_INCREMENT PRIMARY KEY, # BIGINT
	tag varchar(12) default 'new'	
)engine=InnoDB charset=utf8;


CREATE TABLE child (
	child_id int(1) AUTO_INCREMENT PRIMARY KEY,
	description varchar(36) default null,
	parent int(1), # INT
	FOREIGN KEY (parent) REFERENCES parent (reference_id)
)engine=InnoDB charset=utf8;

# SQL Error [1215] [HY000]: Cannot add foreign key constraint

자식 테이블에서 부모 테이블에 참조를 걸어야 하는 key가 꼭 primary key가 아니여도 Unique이면 가능하다.
FOREIGN KEY로 설정이 불가능한 데이터가 있는데 BLOB, TEXT이다.
FOREIGN KEY를 이용하여 테이블이 생성되었으나 모종의 이유로 테이블 혹은 데이터가 삭제가 필요하다면 child → parent 순서로 지우지 않으면 Error가 발생한다. 선언된 제약조건이기 떄문에 그러하다. 만약 이 제약 조건에 관계없이 삭제하고 싶다면 아래 옵션을 사용할 수 있다.
SET foreign_key_checks = 0; # OFF SET foreign_key_checks = 1; # ON

FOREIGN KEY 생성 / 수정 / 삭제

FOREIGN KEY 생성

FOREIGN KEY의 데이터 제약조건을 설명하기 위해 아래 테이블을 사용한다.

CREATE TABLE parent (
	reference_id INT(1) AUTO_INCREMENT PRIMARY KEY,
	tag varchar(12) default 'new'	
)engine=InnoDB charset=utf8;

CREATE TABLE child (
	child_id int(1) AUTO_INCREMENT PRIMARY KEY,
	description varchar(36) default null,
	parent INT(1),
	FOREIGN KEY (parent) REFERENCES parent (reference_id)
)engine=InnoDB charset=utf8;

위와 같이 테이블을 생성하고 child 테이블에 show create table로 생성된 테이블 정보를 확인하면 다음과 같은 결과가 확인된다.

CREATE TABLE `child` (
  `child_id` int(1) NOT NULL AUTO_INCREMENT,
  `description` varchar(36) DEFAULT NULL,
  `parent` int(1) DEFAULT NULL,
  PRIMARY KEY (`child_id`),
  KEY `parent` (`parent`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `parent` (`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

FOREIGN KEY 삭제

FOREIGN KEY는 생성할 때 constraint를 생성하는데 지정하지 않으면 DB에서 알아서 라벨링을 해준다. 보통 TABLENAME_ibfk_1 로 지정된다. 외래키를 삭제할 때는 이렇게 외래키에 라벨링된 이름을 삭제해야된다.

alter table child DROP FOREIGN KEY child_ibfk_1;

주의할 점은 외래키를 삭제하는 것이 정의된 필드를 삭제하는 것이기 때문에 테이블을 정의할 떄 선언했던 키는 남아있다는 점이다.

# 외래키 삭제 전 (SHOW CREATE TABLE child)
CREATE TABLE `child` (
  `child_id` int(1) NOT NULL AUTO_INCREMENT,
  `description` varchar(36) DEFAULT NULL,
  `parent` int(1) DEFAULT NULL,
  PRIMARY KEY (`child_id`),
  KEY `parent` (`parent`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `parent` (`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# 외래키 삭제 후 (SHOW CREATE TABLE child)
CREATE TABLE `child` (
  `child_id` int(1) NOT NULL AUTO_INCREMENT,
  `description` varchar(36) DEFAULT NULL,
  `parent` int(1) DEFAULT NULL,
  PRIMARY KEY (`child_id`),
  KEY `parent` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

FOREIGN KEY 수정

외래키 자체를 수정하는 방법을 찾진 못했다. 그러니 대안으로 기존에 생성된 외래키를 먼저 삭제한 후 외래키를 추가하는 방법으로 수정을 가하도록 하자. 아래는 parent2라는 테이블이 생성되었고 기존에 child 테이블의 외래키가 parent2로 참조를 변경하는 경우를 기술한 것이다.

# 새로운 테이블 생성
CREATE TABLE parent2 (
	parent2_id INT(1) AUTO_INCREMENT PRIMARY KEY,
	tag varchar(12) default 'new'	
)engine=InnoDB charset=utf8;

# 기존에 존재하는 외래키 삭제 
alter table child DROP FOREIGN KEY child_ibfk_1;

# 외래키 추가 
alter table child
ADD CONSTRAINT `child_ibfk_2`
FOREIGN KEY (`parent`) REFERENCES `parent2` (`parent2_id`) ON UPDATE SET NULl ON DELETE SET NULL;

# 외래키를 추가하는 SQL문
ALTER TABLE 테이블명
ADD FOREIGN KEY (테이블의 컬럼명) REFERENCES 부모 테이블명(부모 테이블의 컬럼명)
ON UPDATE 옵션 ON DELETE 옵션;

ON DELETE / ON UPDATE

외래키를 지정할 때는 부모 테이블의 값이 삭제 또는 수정 되는 경우에 따라 자식 테이블에서 참조하는 필드를 어떻게 동작하게 만들건지를 지정할 수 있다. 다음과 같은 종류가 존재하며 ON DELETE / ON UPDATE 둘다 사용 가능하다.

CASCADE: 부모 데이터 삭제/수정 시 데이터도 동시 삭제/수정
SET NULL: 부모 데이터 삭제/수정 시 데이터도 동시 NULL로 처리
SET DEFAULT: 부모 데이터 삭제/수정 시 자식 데이터의 기본값으로 UPDATE
RESTRICT: 자식 테이블에 데이터가 남아있는 경우 부모 테이블의 데이터는 삭제/수정 불가

CASCASDE

DELETE의 경우

CREATE TABLE parent (
	reference_id INT(1) AUTO_INCREMENT PRIMARY KEY,
	tag varchar(12) default 'new'	
)engine=InnoDB charset=utf8;

CREATE TABLE child (
	child_id int(1) AUTO_INCREMENT PRIMARY KEY,
	description varchar(36) default null,
	parent INT(1),
	FOREIGN KEY (parent) REFERENCES parent (reference_id) ON DELETE CASCADE
)engine=InnoDB charset=utf8;

INSERT INTO fastapi.parent (tag) VALUES('new');
INSERT INTO fastapi.child (description, parent) VALUES('', 1);

현재 데이터는 다음과 같다.

# Parent
reference_id|tag|
------------+---+
           1|new|

# Child
child_id|description|parent|
--------+-----------+------+
       1|           |     1|

부모 테이블의 데이터를 삭제된 경우 다음과 같이 동작한다.

# Parent
reference_id|tag|
------------+---+

# Child
child_id|description|parent|
--------+-----------+------+

UPDATE의 경우

UPDATE의 경우 부모 테이블의 값이 변경된 경우 자식 테이블도 변경되는 것이기 때문에 예시가 다르다. 자식 테이블에서는 부모 테이블의 tag라는 값을 참조하고 있는 상황이다.

CREATE TABLE parent (
	reference_id INT(1) AUTO_INCREMENT PRIMARY KEY,
	tag varchar(12) default 'new',
	UNIQUE `index_tag` (tag)
)engine=InnoDB charset=utf8;

CREATE TABLE child (
	child_id int(1) AUTO_INCREMENT PRIMARY KEY,
	description varchar(36) default null,
	tag varchar(12) default 'new',	
	FOREIGN KEY (tag) REFERENCES parent (tag) ON UPDATE CASCADE
)engine=InnoDB charset=utf8;

# DATA INSERT
INSERT INTO fastapi.parent (tag) VALUES('new');
INSERT INTO fastapi.child (description, tag) VALUES('', 'new');

# Parent
reference_id|tag|
------------+---+
           1|new|

# Child
child_id|description|tag|
--------+-----------+---+
       1|           |new|

이제 부모 테이블의 tag를 변경해보자

UPDATE parent set tag='new2' where tag='new';

# child 테이블 변경
child_id|description|tag |
--------+-----------+----+
       1|           |new2|

SET NULL

SET NULL은 다음과 정의 할 수 있다.

CREATE TABLE parent (
	reference_id INT(1) AUTO_INCREMENT PRIMARY KEY,
	tag varchar(12) default 'new'
)engine=InnoDB charset=utf8;

CREATE TABLE child (
	child_id int(1) AUTO_INCREMENT PRIMARY KEY,
	description varchar(36) default null,
	parent  INT(1),	
	**FOREIGN KEY (parent) REFERENCES parent (reference_id) ON DELETE SET NULL**
)engine=InnoDB charset=utf8;

동작 과정은 CASCADE 예시에서 보여준 케이스대로 해당 데이터가 NULL로 변경될 것이기 떄문에 자세한 설명은 생략한다.

SET DEFAULT

이쯤되면 감이 잡혔을 것이다. SET DEFAULT도 설명을 읽으면 이해가 될테니 어떻게 선언할 수 있는지 생략하겠지만 MySQL에서는 SET DEFAULT 선언이 되지 않는다점을 주의하자. MySQL5.7에서는 SET DEFAULT로 스키마를 생성하게 될 경우 다음과 Error가 표출된다.

CREATE TABLE parent (
	reference_id INT(1) AUTO_INCREMENT PRIMARY KEY,
	tag varchar(12) default 'new'
)engine=InnoDB charset=utf8;

CREATE TABLE child (
	child_id int(1) AUTO_INCREMENT PRIMARY KEY,
	description varchar(36) default null,
	parent  INT(1),	
	FOREIGN KEY (parent) REFERENCES parent (reference_id) ON DELETE SET DEFAULT
)engine=InnoDB charset=utf8;


# OutPut
SQL Error [1215] [HY000]: Cannot add foreign key constraint

관련 자료는 MySQL Document를 참고하도록 하자

RESTRICT

Restrict는 자식 테이블에 데이터가 남아있는 경우 부모 테이블의 데이터가 삭제 혹은 수정이 되지 않는 것을 가리킨다.

FOREIGN KEY를 사용하지 않는다면?

환경에 따라 FOREIGN KEY를 사용하지 않는 경우들도 존재하는데 FOREIGN KEY를 사용하지 않는다면 다음과 같은 작업들이 추가로 발생한다.

1. INSERT, UPDATE, DELETE 전에 데이터를 확인하기 위해 SELECT 쿼리를 실행해야 한다
2. 여러 테이블을 변경하기 위해 테이블 잠금을 사용할 필요가 있다.
3. 불가피하게 생기는 고아 데이터를 정정하기 위해 품질 제어 스크립트를 주기적으로 돌려야 한다.

728x90

저작자표시 비영리 변경금지

'Server > DataBase' 카테고리의 다른 글

Oracle SQL : Madang.sql (0)	2022.04.03
Data Base Architecture (0)	2022.04.03
[PostgreSQL] 연습용 DataBase 생성 가이드 (0)	2020.11.17

뭐라도 해야 한다면 기록을..

FOREIGN KEY를 알아보자.

Abstract

Description

FOREIGN KEY에 관련된 제약사항

FOREIGN KEY 생성 / 수정 / 삭제

FOREIGN KEY 생성

FOREIGN KEY 삭제

FOREIGN KEY 수정

ON DELETE / ON UPDATE

CASCASDE

DELETE의 경우

UPDATE의 경우

SET NULL

SET DEFAULT

RESTRICT

FOREIGN KEY를 사용하지 않는다면?

'Server > DataBase' 카테고리의 다른 글

티스토리툴바

FOREIGN KEY를 알아보자.

Abstract

Description

FOREIGN KEY에 관련된 제약사항

FOREIGN KEY 생성 / 수정 / 삭제

FOREIGN KEY 생성

FOREIGN KEY 삭제

FOREIGN KEY 수정

ON DELETE / ON UPDATE

CASCASDE

DELETE의 경우

UPDATE의 경우

SET NULL

SET DEFAULT

RESTRICT

FOREIGN KEY를 사용하지 않는다면?

'Server > DataBase' 카테고리의 다른 글

'Server/DataBase' Related Articles

티스토리툴바