TSQL: NOCHECK foreign key inside CREATE TABLE

Question

I want to add a foreign key constraint to a string with my CREATE TABLE statement. However, I also want to include the NOCHECK attribute. Can this be done in one pass inside the CREATE TABLE statement? I can not find a good example.

So something like:

CREATE TABLE dbo.SampleTable ( [ID] INT IDENTITY (1,1) NOT NULL, [ParentSampleTableID] INT NOT NULL, <NOCHECK> CONSTRAINT [FK_SampleTable_ParentSampleTable] FOREIGN KEY (ParentSampleTableID) REFERENCES dbo.ParentSampleTable ([ID]) ) 

Any ideas?

Answer 1

You cannot add a constraint and disable the table definition level.

You have two options

Do not add a restriction at the table definition level or later. Add a constraint and also disable it with NOCHECK.

 CREATE TABLE dbo.SampleTable ( [ID] INT IDENTITY (1,1) NOT NULL, [ParentSampleTableID] INT NOT NULL) GO ALTER TABLE dbo.SampleTable WITH NOCHECK ADD CONSTRAINT [FK_SampleTable_ParentSampleTable] FOREIGN KEY (ParentSampleTableID) REFERENCES dbo.ParentSampleTable ([ID]) GO 

Add a constraint at the table definition level and later Disable it.

 CREATE TABLE dbo.SampleTable ( [ID] INT IDENTITY (1,1) NOT NULL, [ParentSampleTableID] INT NOT NULL, CONSTRAINT [FK_SampleTable_ParentSampleTable] FOREIGN KEY (ParentSampleTableID) REFERENCES dbo.ParentSampleTable ([ID]) ) GO ALTER TABLE dbo.SampleTable NOCHECK CONSTRAINT [FK_SampleTable_ParentSampleTable] GO