SQL NO CHECK FOREIGN KEY after data, re-enabling query optimizer

Question

I have an SQL Server 2012 db with a foreign key constraint that references Countries.CountryID = States.CountryID

I needed to recreate Countries table so I DROP this FOREIGN KEY from the States table (otherwise it wouldn't allow me to DROP countries)

After I do that, I want to recreate the FK on States but it does not allow me to unless I specify NO CHECK as such:

ALTER TABLE States 
    WITH NOCHECK
    ADD CONSTRAINT FK_StatesCountries FOREIGN KEY (CountryID) REFERENCES Countries(CountryID) 
GO

I didn't realize at first that some of the rows of States had CountryID that didnt have a matching Countries.CountryID record. Obviously WITH NOCHECK allowed me to proceed without error.

Now SQLServer with mark this FK as "not trusted", MSDN says to re-enable query optimizer by checking all constraints. So why is it that the following line does NOT give me an error even though some States have an invalid CountryID?

 ALTER TABLE States
      CHECK CONSTRAINT ALL
 GO

I would think this should have raised an error.

Answer 1

To make the constraints trusted again, you'd need to use this syntax, which admittedly looks a bit strange. The WITH CHECK is what causes the validation of existing data.

 ALTER TABLE States
      WITH CHECK CHECK CONSTRAINT ALL
 GO

As before, this will throw an error until you resolve the States with bad CountryID values.