Creating a FK with NoCheck

Question

Creating a FK with NoCheck

jude.pieries

Default port

Points: 1438
More actions
August 11, 2014 at 5:50 pm

#289701

Is there away to get the SQL FK constrain to trust old data when the FK is created with NoCheck.?
Some things to ponder
- Assume the table where the FK is created is too large and cant afford a significant down time, but still need the query optimizer to trust the data in the FK for optimal query plans.
- As pre-deploy script will validate the older data in the FK columns for consistency

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No.

The NoCheck option means that the old data is not checked for compliance with the constraint and therefore cannot be trusted. If you want the constraint trusted, you need to create it WITH CHECK (which is the default)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PhilPacha Hall of Fame Points: 3645 More actions · Answer 2

There is a way to make an un-trusted constraint (Check or Foreign Key) trusted.

From BOL:

"The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT ALL."

Please note that you might want to test the individual constraint(s) first, but it is not required.

The syntax for that is:

ALTER TABLE [YourSchema].[YourTableName] WITH CHECK CHECK CNSTRAINT [YourConstraintName]

With that test, you can determine if all values meet the constraint's criteria. However, that doesn't make a constraint 'trusted'.

Here is the statement to make all constraints of a table trusted:

ALTER TABLE [YourSchema].[YourTableName] WITH CHECK CHECK CONSTRAINT ALL

If the statement succeeds, your constraints are now trusted.

To ensure any query plans notice the newly-trusted constraints, you will want to follow up with this statement:

EXEC sp_recompile [YourSchema].[YourTableName]

This Stored Procedure causes related Stored Procedures, Views, User-defined Functions for be marked for recompilation, the next time they are executed. On busy systems, this can have a noticeable impact. Please consider the implications before employing it.