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
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?
There doesn't seem to be any data in those tables, judging from both the scripts you posted and from the width of the connectors in the plan. Analyzing query plans on empty tables is largely irrelevant: at one single page read, the optimizer will almost certainly choose a full scan.
I assume you're doing this as some sort of experiment, in real world you should join those tables not use inner EXIST.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With