TSQL: NOCHECK Foreign Key inside CREATE TABLE

Question

I want to add a foreign key constraint inline 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't seem to 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 at table definition level.

You have two Options

DO not add a constraint at Table Definition Level and later Add the constraint and also Disable it using 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 Constraint at 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