SQL with Manoj

I dicussed about disabling FK constraints in one of my previous post to UPDATE/DELETE records to avoid some conflicts, check this [link] for more info.

There is an option while adding a Constraint to a table:

-- Syntax:
ALTER TABLE TABLE_NAME WITH NOCHECK 
ADD CONSTRAINT FK_CONSTRAINT_NAME FOREIGN KEY(COL_NAME_ID)
REFERENCES OTHER_REF_TABLE (OTHER_TABLE_COL_NAME_ID)

But this doesn’t disable the FK constraint, instead it creates the FK constraint without checking existing records.

You will need to issue a separate ALTER TABLE statement to disable the FK constraint:

-- Syntax:
ALTER TABLE TABLE_NAME NOCHECK CONSTRAINT FK_CONSTRAINT_NAME

So, what’s the difference here when we apply ‘WITH NOCHECK’ vs ‘NOCHECK’?

Let’s check this by taking the [Sales].[SalesOrderDetail] table from AdventureWorks database:

USE [AdventureWorks2008R2]
GO

-- Here I just created the script of [Sales].[SalesOrderDetail] table and stripped off the unwanted parts and changed the table name to [Sales].[SalesOrderDetailTemp]
CREATE TABLE [Sales].[SalesOrderDetailTemp](
	[SalesOrderID] [int] NOT NULL,
	[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[OrderQty] [smallint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[SpecialOfferID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[UnitPriceDiscount] [money] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesOrderDetailTemp_SalesOrderID_SalesOrderDetailID] 
 PRIMARY KEY CLUSTERED (
	[SalesOrderID] ASC,
	[SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Let's create a FK constraint with 'WITH NOCHECK' option
ALTER TABLE [Sales].[SalesOrderDetailTemp]  
	WITH NOCHECK 
	ADD CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
	REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE NO ACTION
GO

Now, go to Object Explorer check the Table -> Keys. Right click on the FK and select Modify. Check the following option on the FK Relationship popup box:
– General -> Check existing data on Creation or Re-Enabling = No
– Table Designer -> Enforce foreign key constraint = Yes

This shows that the FK constriant is still enabled but it didn’t validated the existing records while creating the FK.

Now to really disable the FK we will just have to use the NOCHECK option without using the WITH keyword:

ALTER TABLE [Sales].[SalesOrderDetailTemp]  
	NOCHECK CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID]
GO

Now, check again the Table -> Keys in Object Explorer. You will see the second option ‘Enforce foreign key constraint’ has changed to ‘No’ in the FK Relationship popup box.

-- Final cleanup
DROP TABLE [Sales].[SalesOrderDetailTemp]
GO

So, finally we saw the difference in using the NOCHECK option by using ‘WITH’ and without ‘WITH’ keyword.
And you cannot directly add a Disabled Constraint to a table. You will need to create the FK Constraint first then only you can disable it by using another ALTER TABLE statement.