SQL Server – Can Indexes Mitigate Untrusted Constraints?

constraintforeign keyindexsql server

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

Best Answer

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:

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

Related Solutions

Sql-server – How to add a constraint after I’ve truncated a table

Use WITH NOCHECK instead of WITH CHECK to add a constraint that conflicts with existing data. https://stackoverflow.com/questions/529941/whats-the-difference-between-with-check-add-constraint-followed-by-check-constr

Perhaps you are getting the error because you "2. Trucate a table." and not all of them? Maybe you are removing the Authors or the Books and are leaving the records in AuthorBooks that references these other tables.

Sql-server – Allow self-referential foreign keys to be null from an alter-table

If there are no illegal values in C.ParentID, then I am not clear what the problem might be. Is this Microsoft SQL Server?

I created a test case as follows:

Create Table dbo.Hier
(Id  INT IDENTITY Primary KEy,
 ParID INT NULL,
 Other varchar(10));
GO
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Able');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (1, 'Baker');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Charlie');
GO
ALTER TABLE dbo.Hier ADD 
      CONSTRAINT FK_Parent
      FOREIGN    KEY (ParID)
      REFERENCES Hier(ID);
GO
INSERT INTO dbo.Hier (ParID, Other) VALUES (3, 'Delta');
SELECT * FROM dbo.Hier;
GO
DROP TABLE dbo.Hier;
GO

Seems to work fine. If you are on a different SQL Server it might have different behavior. Or fluentMigrator may have a problem.

Best Answer

Related Solutions

Sql-server – How to add a constraint after I’ve truncated a table

Sql-server – Allow self-referential foreign keys to be null from an alter-table

Related Question