Advertisements
Home > SQL Tips > ‘NOCHECK’ vs ‘WITH NOCHECK’… while creating disabled Foreign Key (FK) constraint

‘NOCHECK’ vs ‘WITH NOCHECK’… while creating disabled Foreign Key (FK) constraint


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.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: