Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Execution plan oddity after re-enabling foreign key constraint

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
alt text

After disabling foreign key constraint
alt text

After re-enabling foreign key constraint
alt text

like image 781
dance2die Avatar asked Jul 04 '09 17:07

dance2die


2 Answers

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?

like image 131
A-K Avatar answered Nov 09 '22 05:11

A-K


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.

like image 25
Remus Rusanu Avatar answered Nov 09 '22 03:11

Remus Rusanu