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.