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?

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.