Untrusted Foreign Keys Still Check New Data In SQL Server

Blitzing

---

This comes up enough that I figured I’d write it down with a quick example. Foreign keys, which are next to useless in SQL Server, can become untrusted for a variety of reasons. Why? Because they don’t help with cardinality estimation, and join elimination only happens for inner joins when you don’t select any columns from one table.

How often do you write queries like that?

Becoming Untrusted

---

Probably the most common reasons for foreign keys become not trusted are someone using BULK INSERT without specifying the CHECK_CONSTRAINTS option, or disabling them to make loading data via another method easier, and then re-enabling them with the “wrong” syntax.

This comes up pretty often when looking at sp_BlitzIndex results with clients, who are deeply distrustful of their data.

Let’s look at an easy example.

CREATE TABLE dbo.child(child_id INT PRIMARY KEY, parent_id INT NOT NULL);
CREATE TABLE dbo.parent(parent_id INT PRIMARY KEY);

Here are two tables, which we’ll add a foreign key to. There are all sorts of fun questions we can ask about which way the relationship should go:

• Do all parents need a child?
• Do all children need a parent?

If one if designing a school database, you may have kids with no parent listed for any number of reasons, from death to emancipation. Likewise, you may have parents of children who graduate, but getting rid of them means adding them back if they have another kid go into the school. This is already too complicated for me.

“Pretending”

---

Let’s say we finally settle on which way the relationship should go, and add a foreign key.

--Add a FK
ALTER TABLE dbo.child ADD CONSTRAINT parental_advisory FOREIGN KEY (parent_id) REFERENCES dbo.parent(parent_id);

This means we can have a row in parent that doesn’t exist in child:

INSERT dbo.parent ( parent_id )
VALUES ( 1 );

But we can’t have a row in child that doesn’t exist in parent:

INSERT dbo.child ( child_id, parent_id )
VALUES ( 1, 2 );

We’ll get an error for trying this!

Msg 547, Level 16, State 0, Line 42
The INSERT statement conflicted with the FOREIGN KEY constraint "parental_advisory". The conflict occurred in database "Crap", table "dbo.parent", column 'parent_id'.

Unless…

Call A Lawyer

---

--Disable it
ALTER TABLE dbo.child NOCHECK CONSTRAINT parental_advisory;

If we disable the foreign key, we can put whatever data in we want, including data that violates the foreign key.

We can even re-enable the foreign key without validating data we put into the table:

--Re-enable the foreign key without validating data
ALTER TABLE dbo.child CHECK CONSTRAINT parental_advisory;

If we check on the status of the foreign key, we’ll see that it’s enabled; it’s just not trusted.

SELECT fk.name,
       fk.is_disabled,
       fk.is_not_trusted
FROM sys.foreign_keys AS fk 
WHERE fk.name = 'parental_advisory';

But just having it enabled will prevent bad new data from coming in:

INSERT dbo.child ( child_id, parent_id )
VALUES ( 2, 3 );

We’ll get the same error as above.

If you want to enable the foreign key and validate current data, you need to run the command like this:

--Re-enable the foreign key validating data
ALTER TABLE dbo.child WITH CHECK CHECK CONSTRAINT parental_advisory;

But this will fail if there’s any bad data:

Msg 547, Level 16, State 0, Line 52
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "parental_advisory". The conflict occurred in database "Crap", table "dbo.parent", column 'parent_id'.

Tracking It Down

---

When you have foreign key validation fail, you’ll need to clean data up that doesn’t fit. You can use queries that look something like this to do it.

SELECT * 
FROM dbo.child AS c
WHERE NOT EXISTS
(
    SELECT 1/0
    FROM dbo.parent AS p
    WHERE p.parent_id = c.parent_id
);

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.