patternsqlModerate
Foreign keys become untrusted after bulk insert
Viewed 0 times
afterinsertuntrustedforeignbulkkeysbecome
Problem
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
The create script being used is:
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
Update:
So, after a couple of traces to narrow down possibilities, it seems like a
So my question now is, is there an alternative strategy to using
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])
GOThe 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]
GOThere 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.Solution
A
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
I plan to review with the developers to ensure that each usage of
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.Context
StackExchange Database Administrators Q#103095, answer score: 11
Revisions (0)
No revisions yet.