Does a foreign key constraint constrain null?
In my Activity table, I have a StaffId field. This is the FK of the employee table (which contains the StaffId). When inserting data into Activities table - if StaffId is null it throws FK constraint error !? I checked the "Alllow Nulls" box in SSMS, so why isn't it?
Note. This record is inserted using the DbCommand.ExecuteNonQuery method.
Also note, I just ran an Insert statement using SSMS to insert NULL values and it worked. Maybe this is a more specific C # question !?
source to share
No, take a look at http://msdn.microsoft.com/en-us/library/ms175464.aspx
A FOREIGN KEY constraint does not have to be associated with only a PRIMARY KEY constraint in another table; it can also be defined to reference UNIQUE constraint columns in another table. FOREIGN KEY constraint can contain zero values; however, if any column of the Complex FOREIGN KEY constraint contains null values, the check for all values that make up the FOREIGN KEY constraint is skipped. To ensure that all values of a complex FOREIGN KEY constraint are checked, specify NOT NULL in all participating columns.
you can see another example in MySql, take a look at the following link http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
You can see that it is possible to set NULL
SET NULL: Delete or update a row from the parent table and set the outer column columns or columns in the child table to NULL. This is only valid if the foreign key columns do not have a NOT NULL qualifier. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.
If you specify the action SET NULL, make sure you have not already declared the columns in the child table as NOT NULL.
source to share
This can be done in a DB as @Martin demonstrated. The problem is at your front end. DbCommand has problems with NULL, one of the reasons they created Linq To Sql.
I suggest you try something like this:
if(parameter_is_not_null)
db.AddInParameter(dbCommand, "staffId", DbType.String, staffId.Text);
else
db.AddInParameter(dbCommand, "staffId", DbType.String, DBNull.Value);
source to share