4

I have a table in SQL Server 2005 with a foreign key and it was disable for huge data loading, and then re-enabled:

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 there a way to re-check this just inserted data?

Julio Furquim
  • 85
  • 6

1 Answers1

9

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

alter table table1 with check check constraint fk_1
go

Adding the "with check" option will validate existing data against the constraint. Doing this will also prevent the constraint from becoming untrusted.

If any existing data violates the constraint, you'll get an error that will look like this:

The ALTER TABLE statement conflicted with the CHECK constraint "fk_1".
Community
  • 1
  • 1
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Thank you very much! Worked like a charm! – Julio Furquim Mar 02 '11 at 19:54
  • +1 for the point about untrusted constraints. It's almost never a good idea to enable constraints using WITH CHECK. Always use WITH CHECK CHECK. It's a stupid syntax but it makes all the difference. – nvogel Mar 02 '11 at 21:08