Foreign Key With Nocheck checks foreign key

Although foreign key is created with "with nocheck" option, it checks foreign key. It has to be run nocheck script.

Let's create scenario.  MasterTable is created with PK. DetailTable is created FK references MasterTable with "with nocheck" option.

Create table dbo.MasterTable (Id int identity,  NameSurname varchar(50))

ALTER table  dbo.MasterTable

add constraint PK_Id  primary key (Id)

Create table dbo.DetailTable (Id int identity,  PersonId int)

ALTER table  dbo.DetailTable  with nocheck

add constraint FK_PersonId  foreign key (PersonId) references  dbo.MasterTable (Id)

When the insert command is run the error is risen.

insert into dbo.DetailTable (PersonId) values(12)

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PersonId". The conflict occurred in database "TestDb", table "dbo.MasterTable", column 'Id'.
The statement has been terminated.

In order to to not get this error the following command must be run.

ALTER TABLE [dbo].[DetailTable] NOCHECK CONSTRAINT [FK_PersonId]

After running this command the insert statement runs correctly.

insert into dbo.DetailTable (PersonId) values(12)