Can Indexes mitigate untrusted Constraint

Question

I using sp_blitz and found two tables that have foreign keys that is untrusted. tried to enable the trust with:

ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName;
GO

But got this error message

Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_importsessionrow_importsession". The conflict occurred in database "DatabaseName", table "dbo.importsession", column 'importsessionid'.

So I went and talked to the developers team and they told me that this ok and its how it should look like. Then I just inform them that it could have a huge performance impact on queries. So the question I went back with from Developer team is:

Can this be mitigated with indexes?

I would be very pleased if you can help me answer the Dev team

Regards Håkan

Answer 1

Just for a bit of background on pros/cons to not having FK's in your database feel free to review this post on StackOverflow. As you can see from that article there are some good reasons (performance penalty on insert/update more painful to the business than dirty data) to go without foreign keys and some bad reasons (laziness/ignorance).

I think you should figure out if the Dev team has valid reasons why they don't want FK's or if they are just being lazy. Based on the information you've provided it doesn't seem like they have a well articulated reason why they don't care about the bad data.

As @AaronBertrand hinted if the Dev team is adamant that FK's shouldn't be enforced, AND business doesn't care about dirty data then you should probably remove the FK constraint. If the business cares about dirty data and the dev team doesn't then that is a whole 'nother ball of wax. Keep in mind, you can still use indexes to make queries faster regardless of whether you have FK relationships between your tables. You just won't get the performance benefits of a FK. In general terms this is like how a non-covering nonclustered index scan is faster than a table scan, but a covering nonclustered covering index scan is even faster.

In summary here is your plan of attack:

1. Get a real answer why the Dev team doesn't want FK's to be enforced.
2. .....
3. Make your queries faster.