>>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?

yes but why would you allow invalid value in SITE_NO?

>
>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?

yes

>
>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.