데이터베이스 외래키 - deiteobeiseu oelaeki

1. 외래키 (Foreign Key)란?

외래키는 두 테이블을 서로 연결하는 데 사용되는 키이다.

외래키가 포함된 테이블을 자식 테이블이라고 하고 외래키 값을 제공하는 테이블을 부모 테이블이라한다.

2. 외래키 사용시 주의 사항

1) 외래키 값은 NULL이거나 부모 테이블의 기본키 값과 동일해야한다. (참조 무결성 제약조건)

2) 부모 테이블의 기본키, 고유키를 외래키로 지정할 수 있다.

3) 부모 테이블의 기본키, 고유키가 여러개의 컬럼으로 이루어져 있다면 부모가 가진 기본키, 고유키 컬럼을 원하는 개수만큼 묶어서 외래키로 지정할 수 있다. 

CREATE TABLE `parent` (
	`id1` INT(11) NOT NULL,
	`id2` INT(11) NOT NULL,
	`id3` INT(11) NOT NULL,
	`uk1` INT(11) NOT NULL,
	`uk2` INT(11) NOT NULL,
	`uk3` INT(11) NOT NULL,
	PRIMARY KEY (`id1`, `id2`, `id3`),
	UNIQUE KEY (`uk1`, `uk2`, `uk3`)
);

CREATE TABLE `child` (
	`id` INT(11) NOT NULL,
	`id1` INT(11) NOT NULL,
	`id2` INT(11) NOT NULL,
	`uk1` INT(11) NOT NULL,
	`uk2` INT(11) NOT NULL,
	PRIMARY KEY (`id`),
	FOREIGN KEY (`id1`, `id2`) REFERENCES `parent` (`id1`, `id2`),
	FOREIGN KEY (`uk1`, `uk2`) REFERENCES `parent` (`uk1`, `uk2`)
)

4) 외래키로 지정할 두 테이블의 필드는 같은 데이터 타입이어야 한다.

3. 외래키 예제

데이터베이스 마다 선언 하는 방식이 다를 수 있으며, Mysql 기준으로 설명하겠다.

create table department(
  	id int auto_increment primary key,
	name varchar(20) not null,
	code char(13) not null unique key
);

create table employee (
  	id int auto_increment primary key,
	name varchar(20) not null,
	code char(13) not null unique key,
	dept_id int,
	foreign key (dept_id) references department(id)
);

department(부서)와 employee(회사원) 테이블이 있다. department이 부모 테이블이고, employee가 자식 테이블이다.

외래키를 가진 테이블이 자식 테이블이고, 참조되는 테이블이 부모 테이블이다. 

  
  CONSTRAINT [CONSTRAINT_NAME] FOREIGN KEY (자식 테이블 컬럼 명) REFERENCES 참조테이블(부모 테이블 기본키명) 
  ON UPDATE 옵션 ON DELETE 옵션;
  
  # CONSTRAINT [CONSTRAINT_NAME]은 생략이 가능하다.
  

4. 외래키 옵션

1) On Delete

 Cascade : 부모 데이터 삭제 시 자식 데이터도 삭제 

 Set null : 부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Null로 업데이트

 Set default : 부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트

 Restrict : 자식 테이블이 참조하고 있을 경우, 데이터 삭제 불가

No Action : Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.

  2) On Update

 Cascade : 부모 데이터 업데이트 시 자식 데이터도 업데이트 

 Set null : 부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Null로 업데이트

 Set default : 부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트

 Restrict : 자식 테이블이 참조하고 있을 경우, 업데이트 불가

 No Action : Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.

5. 외래키 추가

ALTER TABLE employee
ADD FOREIGN KEY (dept_id) REFERENCES department(id);

6. 외래키 삭제

외래키를 삭제하려면 CONSTRAINT_NAME을 알아야한다.

select * 
from information_schema.table_constraints
where TABLE_SCHEMA = 'DB명' and TABLE_NAME = '테이블명'

삭제하고 싶은 키의 CONSTRAINT_NAME을 확인한다.

ALTER TABLE [Table_Name]
DROP CONSTRAINT [CONSTRAINT_NAME];

또는

ALTER TABLE [Table_Name]
DROP FOREIGN KEY [CONSTRAINT_NAME];

7. TEST

# 외래키 이름을 검색한다. ex) employee_ibfk_1
select * 
from information_schema.table_constraints
where TABLE_SCHEMA = 'myDB' and TABLE_NAME = 'employee'

# 외래키를 삭제한다.
ALTER TABLE employee
DROP FOREIGN KEY employee_ibfk_1;

#새로운 조건의 외래키를 추가한다.
#부모 행이 삭제되었을 경우 외래키 ID를 NULL로 업데이트한다.
ALTER TABLE employee
ADD CONSTRAINT employee_ibfk_1 FOREIGN KEY (dept_id) REFERENCES department(id) ON DELETE SET NULL;

도움이 되셨다면 공감버튼을 눌러주세요!

키 개념은 아래 링크를 보면 잘 알 수 있다. 

키의 종류 

https://terms.naver.com/entry.nhn?docId=3431147&cid=58430&categoryId=58430&expCategoryId=58430

수퍼키

https://terms.naver.com/entry.nhn?docId=3431148&cid=58430&categoryId=58430&expCategoryId=58430

레코드들을 식별할 수 있는 ‘필드의 집합’ (유일성)

테이블은 적어도 1개의 슈퍼키를 가져야 한다.

후보키

https://terms.naver.com/entry.nhn?docId=3431149&cid=58430&categoryId=58430&expCategoryId=58430

슈퍼키에서 레코드를 식별할 수 있는 최소한의 필드만 남겨놓은 집합 (유일성, 최소성)

Ex. 슈퍼키 { 학번, 학생이름, 학과 } 집합은 학번만으로도 레코드를 식별할 수 있으므로 부분집합 중 { 학번 } 집합만 후보키가 될 수 있다.

기본키

https://terms.naver.com/entry.nhn?docId=3431150&cid=58430&categoryId=58430&expCategoryId=58430

설계자가 여러 후보키 중 하나를 선택하여 정의한 식별자 (유일성, 최소성)

기본키의 모든 필드의 값은 null 이 될 수 없다.

외래키

https://terms.naver.com/entry.nhn?docId=3431152&cid=58430&categoryId=58430&expCategoryId=58430

다른 테이블의 기본키를 참조한다.

외래키의 모든 필드는 참조하는 기본키와 동일한 도메인(값의 종류&범위)을 갖는다.

외래키의 모든 필드의 값은 참조하는 기본키와 동일하거나 null 이다.

대체키

기본키로 선택되지 못한 후보키들이다. 

이름에서 알 수 있듯이 대체키는 기본키를 대신할 수 있지만 기본키가 되지 못하고 탈락한 이유가 있을 수 있다. 

기본키를 선택할 때 고려할 사항을 하나씩 따져보면 기본키의 [주소 속성이 추가된 릴레이션의 예]의 고객 릴레이션은 고객아이디 속성을 기본키로 선택하는 것이 무난하다. 

따라서 기본키로 선택되지 못한 (고객이름, 주소) 속성 집합이 대체키가 된다. 

키는 관계형 데이터 모델에서 특정 레코드를 식별하기 위해 사용한다. 두 개 이상의 필드로 구성된 키를 복합키라고 한다. 키의 종류에는 슈퍼키, 후보키, 기본키, 외래키, 대체키가 있다.

기본키

https://www.w3schools.com/sql/sql_primarykey.asp

개인키를 테이블 생성 시에 삽입하는 방법은 아래와 같다.

MySQL:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

PRIMARY KEY (ID)

);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int

);

여러 필드를 PRIMARY KEY를 묶기 위한 방법이다.

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)

);

ALTER TABLE Persons

ADD PRIMARY KEY (ID);

ALTER TABLE Persons

ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

를 통해 PRIMARY KEY를 수정 할 수 있고

ALTER TABLE Persons

DROP PRIMARY KEY;

ALTER TABLE Persons

DROP CONSTRAINT PK_Person;

를 통해 기본키로 구성되어있던 것을 DROP 할 수 있다.

외래키

https://www.w3schools.com/sql/sql_foreignkey.asp

FOREIGN KEY Constraint

외래키는 두 테이블을 함께 쓸 때 연결하는 키이다.

외래키는 또다른 테이블에서 기본키를 지칭하기 위한 필드(or collection of fields)이다.

외래키를 포함하는 테이블은 child table라고 보통 말하고 후보키를 포함하는 테이블은 parent table라고 불린다.

MySQL:

CREATE TABLE Orders (

OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,

PRIMARY KEY (OrderID),

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (

OrderID int NOT NULL PRIMARY KEY,

OrderNumber int NOT NULL,

PersonID int FOREIGN KEY REFERENCES Persons(PersonID)

);

를 이용하여 외래키 설정이 가능하다.

외래키를 여러 컬럼을 함께 설정하고싶거나 CONSTRAINT를 이용하기 위해서는 아래와 같이 이용한다.

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (

OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,

PRIMARY KEY (OrderID),

CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)

REFERENCES Persons(PersonID)

);

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders

ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

외래키를 추가하기 위해서는 위와 같이 이용한다.

MySQL:

ALTER TABLE Orders

DROP FOREIGN KEY FK_PersonOrder;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders

DROP CONSTRAINT FK_PersonOrder;

외래키를 DROP 하기 위해서는 위와 같이 사용한다.