How to double-check the primary / foreign key constraint for data already in a table on sql server?

Question

I have a table in SQL Server 2005 with a foreign key, and it was turned off for a huge data load, and then turned on again:

Example:

alter table table1 nocheck constraint fk_1 go lots of inserts... go alter table table1 check constraint fk_1 go 

Now the question is: is there a way to double-check this newly inserted data?

Answer 1

The syntax looks a little silly when the word "check" is repeated, but you want:

 alter table table1 with check check constraint fk_1 go 

Adding the "with validation" option checks existing data for restriction. It will also prevent untrusted restriction.

If any existing data violates the restriction, you will receive an error message that looks like this:

 The ALTER TABLE statement conflicted with the CHECK constraint "fk_1".