>>I just did a little test, after setting the FK (as per code below). Then I added a couple of records to the EQ_FILE with different value in SITE_NO and NULL in CATEGORY. And no problem; no violation of FK constraint. Does it mean the SQL Server automatically ignores NULL values when checking FK constraints?
>>
>
>SQL Server does not enforce FK when any child table column value in FK relationship is null. Which means, when CATEGORY is null, SITE_NO will not be enforced

I want to make sure I understand what you said about "when CATEGORY is null, SITE_NO will not be enforced". Here is a real case example:

Primary Key Table - CALPMCAT:
CATEGORY - Cannot be NULL. Has Unique Value Index
SITE_NO - Cannot be NULL

Child/Foreign Key Table - EQ_FILE
CATEGORY - Can be NULL
SITE_NO - Cannot be NULL

My understanding of what you said is that if a user adds a record to the Child table (EQ_FILE) with value in CATEGORY equals to NULL and SITE_NO equals to some number that does not exist in Primary Key Table (CALPMCAT), the FK constraint violation will not fire. Correct?

Then when user changes the value in this record of the EQ_FILE field CATEGORY from NULL to some non-null value, the FK violation fires.
Correct?

I don't see a problem with the above scenario since the business of the application should not allow adding a record to EQ_FILE and setting a value to CATEGORY if it does not exist in the Primary Key Table.