SQL Server – Foreign Keys Become Untrusted After Bulk Insert

bulk-insertforeign keysql serversql server 2014

In a SQL 2014 edition server (12.0.2430.0 – no SP1 yet)
with a database in 2012 compatibility mode (working on getting it switched to 2014…) I have a handful of foreign key objects which are consistently marked as not trusted in the database. I've dropped and re-created them without NOCHECK options, but within 5-10 minutes they become untrusted again and if I generate a CREATE script it comes out as:

ALTER TABLE [dbo].[Points]  WITH NOCHECK 
ADD  CONSTRAINT [FK_BadgeId] FOREIGN KEY([BadgeId])
REFERENCES [dbo].[Badge] ([Id])
GO

The create script being used is: 

ALTER TABLE [dbo].[Points]
ADD  CONSTRAINT [FK_BadgeId] FOREIGN KEY([BadgeId])
REFERENCES [dbo].[Badge] ([Id])
GO

ALTER TABLE [dbo].[Points] CHECK CONSTRAINT [FK_BadgeId]
GO

There is no replication, there are no third party tools, and I am monitoring all DDL statements on the database so it isn't another user.

I am able to check the constraints fine (using WITH CHECK CHECK on each) but they still become untrusted shortly after. Only maintenance jobs that run are Ola's in the early AM and this happens throughout the day.

Update:

So, after a couple of traces to narrow down possibilities, it seems like a BULK INSERT may be causing the FK to become untrusted. This msdn question states that this is a valid route for the key to become untrusted, which is the first I've heard of it.

So my question now is, is there an alternative strategy to using BULK INSERT that can maintain foreign key is_trusted status? It's being executed in the context of an application running several times per hour. I could have the developers batch their insert statements instead, but I'd prefer not to put an ultimatum on using BULK INSERT if I don't have to.

Best Answer

A BULK INSERT from our application was the cause of untrusted foreign keys, as confirmed by an MSDN question. Similar to BCP, anytime a BULK INSERT is performed then the foreign key is not checked during insert, therefor making it not trusted.

As Kin mentioned, there are simple scripts available to find and fix untrusted foreign keys, but in my scenario the inserts are happening at frequency which makes effort to constantly fix this issue not worth it.

And ypercube suggested using CHECK_CONSTRAINTS option within the bulk inserts which would force the constraint to be obeyed.

I plan to review with the developers to ensure that each usage of BULK INSERT is justified in terms of rows inserted, otherwise it will have to be something to live with.

Related Question