Execution plan oddity after re-enabling foreign key constraint
sql, sql-execution-plan, sql-server, t-sql
Solution
Most likely your constraint is enabled but not trusted, so there can be orphan rows in your child table. Read this great post by Hugo Kornelis:Can you trust your constraints?
Problem
I have a weird problem where after setting `nocheck` on a foreign constraint and re-enabling it, I am getting a same out-dated execution plan that was used with `nocheck` on. Why would SQL server generate an execution plan as if foreign constraint `FKBtoA` is disabled even after adding the check again with following statement? ``` alter table B check constraint FKBtoA ``` [UPDATE1] So far dropping foreign constraint and readding it worked. ``` alter table B drop constraint FKBtoA alter table B add constraint FKBtoA foreign key (AID) references A(ID) ``` But for really big tables, this seems like an overkill - Is there a better way? [ANSWER] I had to add `WITH CHECK` in alter statement like following to get the old execution plan ``` alter table B WITH CHECK add constraint FKBtoA foreign key (AID) references A(ID) ``` Here is a full SQL statement ``` create table A ( ID int identity primary key ) create table B ( ID int identity primary key, AID int not null constraint FKBtoA references A (ID) ) select * from B where exists (select 1 from A where A.ID = B.AID) alter table B nocheck constraint FKBtoA GO select * from B where exists (select 1 from A where A.ID = B.AID) alter table B check constraint FKBtoA GO select * from B where exists (select 1 from A where A.ID = B.AID) ``` Here is the screenshot of execution plans per each `SELECT` statement Before disabling foreign key constraint After disabling foreign key constraint After re-enabling foreign key constraint