When do foreign keys become untrusted after bulk?
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.
How can I tell if a database has untrusted foreign keys?
There is a simple way to detect whether a database contains one or more untrusted foreign keys. It’s simply by querying the sys.foreign_keys view in that database and check the is_not_trusted column. If this column is set to 1, it means the constraint is untrusted.
Can a Bulk insert cause a FK to become untrusted?
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.
How to check foreign keys or check constraints not trusted?
First, get the list of affected foreign keys with this T-SQL script. It must be run inside the affected database (s): (Thanks to Aaron Lowe @Vendoran for improving this script!) You have to tell SQL Server to not just enable the constraint, but to recheck all of the data that’s been loaded.
Why are my foreign keys not trusted by me?
Probably the most common reasons for foreign keys become not trusted are someone using BULK INSERT without specifying the CHECK_CONSTRAINTS option, or disabling them to make loading data via another method easier, and then re-enabling them with the “wrong” syntax.
How is foreign key verified in SQL Server?
In the insert operations, SQL Server checks the inserted child row that exists in the parent table to verify the referential integrity. When a foreign key status is not trusted then the storage engine does not require to check the inserted foreign key is exists in the parent table.