Message
From
29/08/2016 16:52:51
To
29/08/2016 16:36:26
General information
Forum:
Microsoft SQL Server
Category:
Other
Miscellaneous
Thread ID:
01639981
Message ID:
01640152
Views:
46
>>>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.
>>
>>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.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham