How does SqlBulkCopy circumnavigate foreign key constraints?

Question

I used SqlBulkCopy to insert a collection of rows into a table. I forgot to set an integer value on the rows. The missing column is used to reference another table and this is enforced with a foreign key constraint.

For every row inserted, the final integer value was zero and zero didn't identify a row in the related table. When I modified the value to a valid value and then tried to switch it back to zero it wouldn't accept it.

So my question is how does SqlBulkCopy manage to leave the database in an invalid state?

Flagstone

Answer 1

how does SqlBulkCopy manage to leave the database in an invalid state?

It disables foreign keys on the table you are inserting into.

Yes, this is a horrible default. Be sure to set the option CHECK_CONSTRAINTS (or CheckConstraints for SqlBulkCopy) if you can at all afford it.

It also by default does not fire triggers which is equally terrible for data consistency. The triggers are there for a reason.

Burress

But when it turns the foreign keys back on, why doesn't it throw an exception at that point? – Flagstone

It does not tun them on. Foreign keys can be in a DISABLED state in SQL Server that is permanent. From this point on your FK does nothing, until you take action manually. – Burress

I didn't manually turn the FK back on yet it stopped me adding new values which contradicted it. So the FK was working again after the bulk insert. – Flagstone

Ok, right. FKs have two independent disabled states: disabled and not trusted. Both are visible in the metadata views. Not trusted means that there might be invalid rows and the engine does not use the properties of the FK for query optimization. It might interest you to see what statements SqlBulkCopy sends. Run SQL Profiler. – Burress

Answer 2

By default CHECK and FOREIGN KEY constraints are ignored during bulk copy operation. SqlBulkCopy is a managed class providing functionality similar to what SQL Server bcp command offers. The bcp command has a -h hint and unless you provide the CHECK_CONSTRAINTS hint the CHECK and FOREIGN KEY constraints are ignored during the bulk load. The technet article states that - http://technet.microsoft.com/en-us/library/ms162802.aspx

Similarly SqlBulkCopy class has a constructor which accepts SqlBulkCopyOptions enum. You would have to set the CheckConstraints enum option to ensure constraints are checked - http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions(v=vs.110).aspx

Here is an article that talks about constraint check controlling - http://technet.microsoft.com/en-us/library/ms186247(v=sql.105).aspx

Hope this helps.

Cleptomania

I noticed this question and I wonder if it is still the case using Azure Sql 2017?! – Plast