제약 조건 Enable 할 때 주의할 점 - WITH CHECK 옵션

마이그레이션 등의 이유로 제약 조건을 Disable했다가, 다시 Enable하는 경우
ALTER TABLE ~ NOCHECK ~ / ALTER TABLE ~ CHECK ~ 구문을 사용할 수 있다.

그런데 ALTER TABLE ~ CHECK ~ 구문을 사용해 제약 조건을 다시 Enable하는 경우 WITH CHECK 옵션을 사용하지 않으면, 제약 조건이 Enable되기 전에 입력되어 있던 값을 DB엔진이 신뢰하지 않는데... 이 때문에 옵티마이저의 삽질(?)을 구경하게 될지 모른다는 문제가 있다.

결론은 제약 조건을 Enable하는 경우 반드시 WITH CHECK 옵션을 사용하자는 얘기다.

-- 테이블생성

CREATE TABLE dbo.Employee (

  employeeID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

  firstName varchar(50) NOT NULL,

  lastName varchar(50) NOT NULL

);

 

CREATE TABLE dbo.TimeCard

(

  timeCardID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

  employeeID int NOT NULL,

  hoursWorked tinyint NOT NULL,

  dateWorked datetime NOT NULL

);

GO 

-- 참조키생성

ALTER TABLE dbo.TimeCard

  ADD CONSTRAINT FK_employeeID_TimeCard FOREIGN KEY (employeeID)

  REFERENCES dbo.Employee(employeeID);

GO

-- 데이터입력

INSERT dbo.Employee (firstName, lastName)

VALUES ('JOHN', 'DOE');

 

INSERT dbo.TimeCard (employeeID, hoursWorked, dateWorked)

VALUES (1, 8, '2008-01-01');

GO

 

-- 참조키가신뢰할수있는상태인지확인

SELECT

  CASE

    WHEN OBJECTPROPERTY(OBJECT_ID('FK_employeeID_TimeCard'), 'CnstIsNotTrusted') = 1 THEN 'NO'

    ELSE 'YES'

  END AS 'IsTrustWorthy?';

GO

 

-- 참조키Disable 하기

ALTER TABLE dbo.TimeCard NOCHECK CONSTRAINT FK_employeeID_TimeCard;

GO

 

-- 제약조건을위배하지않는데이터추가입력

INSERT dbo.TimeCard (employeeID, hoursWorked, dateWorked)

VALUES (1, 8, '2008-01-04');

GO

 

-- WITH CHECK 옵션없이참조키Enable 하기

ALTER TABLE dbo.TimeCard CHECK CONSTRAINT FK_employeeID_TimeCard;

GO

 

-- 참조키제약조건위배확인하기

DBCC CHECKCONSTRAINTS ('dbo.TimeCard')

GO

이상없다는 결과 (당연한 얘기)

 

-- 참조키가신뢰할수있는상태인지확인

SELECT

  CASE

    WHEN OBJECTPROPERTY(OBJECT_ID('FK_employeeID_TimeCard'), 'CnstIsNotTrusted') = 1 THEN 'NO'

    ELSE 'YES'

  END AS 'IsTrustWorthy?';

GO

 

-- WITH CHECK 옵션으로참조키Enable 하기

ALTER TABLE dbo.TimeCard WITH CHECK CHECK CONSTRAINT FK_employeeID_TimeCard;

GO

 

-- 참조키가신뢰할수있는상태인지확인

SELECT

  CASE

    WHEN OBJECTPROPERTY(OBJECT_ID('FK_employeeID_TimeCard'), 'CnstIsNotTrusted') = 1 THEN 'NO'

    ELSE 'YES'

  END AS 'IsTrustWorthy?';

GO 

(참고 사이트 : http://www.mssqltips.com/tip.asp?tip=1539)