[MSSQL] 외래키(FOREIGN KEY) 제약조건 - 생성(추가), 삭제

MSSQL에서 테이블에 FOREIGN KEY 제약조건을 추가해야 할 경우가 있다.

이번 시간에는 FOREIGN KEY 제약조건 생성(추가), 삭제 방법에 대해서 설명한다.

 

외래키(FOREIGN KEY)란?

- SQL DB는 특정한 열을 외래키로 지정할 수 있다.

- 외래키는 어떤 테이블에 있는 특정 열이, 다른 테이블에 있는 열(보통 기본키)를 참조하는 것이다

아래의 예제를 확인하며 실습해보자.

 

1.FOREIGN KEY 제약을 갖는 테이블 만들기

-- 테이블이 이미 있으면 삭제

IF OBJECT_ID('dbo.Vacation', 'U') IS NOT NULL

DROP TABLE dbo.Vacation

GO



-- FOREIGN KEY 제약을 갖는 테이블 만들기

CREATE TABLE dbo.Vacation (

VacationID int IDENTITY PRIMARY KEY,

EmpID char(5) REFERENCES dbo.Employee(EmpID), -- FOREIGN KEY 제약

BeginDate date NOT NULL,

EndDate date NOT NULL,

Reason nvarchar(50) DEFAULT N'개인사유',

Duration AS (DATEDIFF(day, BeginDate, EndDate) + 1),

CHECK (EndDate >= BeginDate)

)

GO

 

2.FOREIGN KEY 제약 삭제

-- FOREIGN KEY 제약 이름 확인

SELECT name

FROM sys.foreign_keys

WHERE parent_object_id = OBJECT_ID('dbo.Vacation', 'U') AND type = 'F'

GO

-- 테이블에서 FOREIGN KEY 제약 삭제

ALTER TABLE dbo.Vacation

DROP CONSTRAINT FK__Vacation__EmpID__04AFB25B

GO

 

3.기존 테이블에 FOREIGN KEY 제약 추가

-- 테이블에 FOREIGN KEY 제약 추가

ALTER TABLE dbo.Vacation

ADD CONSTRAINT FK_EmpID FOREIGN KEY (EmpID) REFERENCES dbo.Employee(EmpID)

GO

 

4.단순 FOREIGN KEY 제약 설정

-- dbo.Vacation 테이블 삭제

IF OBJECT_ID('dbo.Vacation', 'U') IS NOT NULL

DROP TABLE dbo.Vacation

GO



-- dbo.Employee 테이블 삭제

IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL

DROP TABLE dbo.Employee

GO



-- dbo.Employee 테이블 만들기

CREATE TABLE dbo.Employee (

EmpID char(5) PRIMARY KEY,

EmpName nvarchar(10) NOT NULL,

EngName varchar(20) NULL,

)

GO



-- 데이터 추가

INSERT INTO dbo.Employee(EmpID, EmpName, EngName) VALUES('S0001', N'홍길동', 'Gildong')

INSERT INTO dbo.Employee(EmpID, EmpName) VALUES('S0002',N'일지매')

INSERT INTO dbo.Employee(EmpID, EmpName, EngName) VALUES('S0003',N'강우동', 'NULL') 

GO



-- dbo.Employee 테이블 만들기

CREATE TABLE dbo.Vacation (

VacationID int IDENTITY PRIMARY KEY,

EmpID char(5) NOT NULL,

BeginDate date NOT NULL,

EndDate date NOT NULL,

Reason nvarchar(50) DEFAULT N'개인사유',

Duration AS (DATEDIFF(day, BeginDate, EndDate) + 1),

CHECK (EndDate >= BeginDate)

)

GO



-- 데이터 추가

INSERT INTO dbo.Vacation VALUES('S0001','2011-01-12','2011-01-12',N'감기몸살')

INSERT INTO dbo.Vacation VALUES('S0001','2011-03-21','2011-03-21',N'글쎄요')

INSERT INTO dbo.Vacation VALUES('S0002','2012-02-10','2012-02-13',N'두통')

INSERT INTO dbo.Vacation VALUES('S0003','2012-09-17','2012-09-17',N'휴식이 필요')

GO



-- FOREIGN KEY 제약 추가(제약 이름을 명시)

ALTER TABLE dbo.Vacation

ADD CONSTRAINT FK_Vacation_EmpID

FOREIGN KEY (EmpID) REFERENCES dbo.Employee(EmpID)

GO



--테이블 조회

 

-- 직원 정보 삭제

외래키 제약조건으로 EmpID = 'S0003' 데이터 삭제가 불가능하다.

DELETE dbo.Employee

WHERE EmpID = 'S0003'

GO

/*

메시지 547, 수준 16, 상태 0, 줄 323

DELETE 문이 REFERENCE 제약 조건 "FK_Vacation_EmpID"과(와) 충돌했습니다.

데이터베이스 "HRDB2", 테이블 "dbo.Vacation", column 'EmpID'에서 충돌이 발생했습니다.

문이 종료되었습니다.

*/