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.
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.