Click here to Skip to main content
15,880,543 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more: , +
what is difference between ‘WITH NO CHECK’ and ‘NO CHECK’ in SQL Server ??
Posted

1 solution

Using WITH NO CHECK:

We have a table with 100 records.
1)Now we are creating a foreign Key Constraint by altering that table

SQL
ALTER TABLE [Sales].[SalesOrderDetailTemp]
    WITH NOCHECK
    ADD CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
    REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])


Here by using NOCHECK option
-->Foreign key does not Checks for old 100 records exists in References table (Salesorderheader)
-->Instead of Foreign key will check for New record inserted.

Using NOCHECK:
SQL
ALTER TABLE [Sales].[SalesOrderDetailTemp]
    NOCHECK CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID]

This option Disables the Constraint of that Particular table
 
Share this answer
 
Comments
Osama Abu Sitta 11-May-15 7:04am    
Can I use the below query :

ALTER TABLE [Sales].[SalesOrderDetailTemp]
WITH NOCHECK NOCHECK
ADD CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
Member 11039257 11-May-15 7:08am    
I just gave an example for With NOCHECK. whether u have similar scenario?
.
Osama Abu Sitta 11-May-15 7:11am    
Thank, I just ask if I can use (WITH NOCHECK NOCHECK) in any query in add FOREIGN KEY
Member 11039257 11-May-15 7:19am    
Yes u can use ,but it should be old tables which already have some records.
Osama Abu Sitta 11-May-15 7:32am    
Thanks a lot

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900