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

Again, continue on a real case scenario. User logs into the program and selects SITE_NO 20. That is, the Site No table already has 20 or more records (that is, the customer is licensed to use so many Sites). He/she then adds a record to the EQ_FILE and the record is automatically (by the application) is assigned to the currently selected site (that is, SITE_NO 20). But the Primary Key Table (CALPMCAT) does not yet have a record for this site. And it is possible that if a user does not want to use the field CATEGORY in EQ_FILE, the CALPMCAT will never have a record for SITE_NO 20. So the business allows them to add a record with "invalid" value in SITE_NO. This is why I am putting invalid in quotation marks.