Foreign key column allows 0 in SQL Server from bulk insert

Question

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

This insertion occurs in a bulk insertion with .NET. However, if I change the value to a real number and return to 0 in SQL Server directly, it will not allow it.

So, somehow volume insertion allows to resolve 0, is there any reason?

Answer 1

By default, it BULK INSERTignores validation and foreign key restrictions. You can enable them using CHECK_CONSTRAINTS.

This is explained in the documentation:

CHECK_CONSTRAINTS

, . CHECK_CONSTRAINTS, CHECK FOREIGN KEY , .