Foreign key column allows 0 in SQL Server from bulk insert

.net foreign-keys sql sqlbulkcopy sql-server

Question

I have a column that checks FK constraints on a column and allows NULL. However, it is allowing 0 as a value even though there is no row for ID of 0 in the primary table.

This insert happens on a bulk insert with .NET. However, if I change the value to a valid number and back to 0 in SQL Server directly then it won't allow it.

So somehow the bulk insert is allowing the 0 to be allowed, is there a reason why?

Accepted Answer

By default, BULK INSERT ignores check and foreign key constraints. You can turn them on using CHECK_CONSTRAINTS.

This is explained in the documentation:

CHECK_CONSTRAINTS

Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS option, any CHECK and FOREIGN KEY constraints are ignored, and after the operation, the constraint on the table is marked as not-trusted.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why