[MSSQL] SET NULL 옵션(외래키 설정 시 자동삭제 및 업데이트)

SET NULL 란?

부모테이블에서 primary 값이 수정 또는 삭제될 경우 하위테이블의 reference값은 존재할 수 없습니다.

옵션이 없을 경우는 에러가 발생하고 옵션 SET NULL 로 정의되면 하위테이블의 reference값이  NULL 값으로 변경되면서 참조무결성을 유지합니다.

 

ON DELETE SET NULL : 기준 테이블의 데이터가 삭제되었을 때 외래 키 테이블의 데이터들도 자동으로 NULL 값으로 변경

ON UPDATE SET NULL  : 기준 테이블의 데이터가 변경되었을 때 외래 키 테이블도 자동으로 NULL 값으로 변경

 

관련 테이블 테이블생성

작업 전 필요한 테이블 및 데이터를 입력한다.

-- 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

 

1.SET NULL 옵션 설정

-- EmpID에 NULL 값 허용

ALTER TABLE dbo.Vacation

ALTER COLUMN EmpID char(5) NULL

GO



-- FOREIGN KEY 제약 추가(SET NULL)

ALTER TABLE dbo.Vacation

ADD CONSTRAINT FK_Vacation_EmpID

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

ON DELETE SET NULL   

ON UPDATE SET NULL

GO

 

-- 직원 정보 삭제

DELETE dbo.Employee

WHERE EmpID = 'S0002'

GO



-- 확인

SELECT * FROM dbo.Vacation

GO