January 14, 2009 at 1:13 pm
I'm hoping one of you more experienced T-SQL veterans can point me to a good internet resource detailing the use of NUll values in a Foreign Key.
The company that prodcues the primary app we use has a new Foreign Key Contsraint in their latest product update and its supposed to alloow Null values Foreign Keys. So every row of dat in the CHILD table can have a Foreign Key value that is equal to the Primary Key value of a row in the Parent table or a row in the Child table can have a Null in the Foreign Key.
I'm not saying there's no need to do this, clearly ther emust be else why include the NOCHECK option in Transact-SQL. However it's not the default logic and I'm having a hard tiem seeing why this should be setup to allow Null Foreign keys.
Any comments and or links/references are greatly appreciated.
Thanks
Kindest Regards,
Just say No to Facebook!January 14, 2009 at 1:19 pm
Null in an FK value just means it doesn't have a parent relationship. Most of the time, that doesn't make sense, but sometimes it does. It depends on whether you define null as "unknown value" or "no value" in that context.
For an example of where Null in an FK makes sense, how about a standard hierarchy table? You'll have an ID and a parent ID in the table. The parent ID is an FK to another row's ID, in the same table. Traditionally, in this structure, a null parent ID means "no value", which means that row has no parent and is the top of a hierarchy structure.
(Some people don't like that use of null, or don't like that way of defining hierarchies, or both, but it's a simple example of a null FK that makes some sense.)
So, depending on your tables, it might make sense for some rows in the second table to have no reference for the FK.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 1:24 pm
GSquared (1/14/2009)
Null in an FK value just means it doesn't have a parent relationship. Most of the time, that doesn't make sense, but sometimes it does. It depends on whether you define null as "unknown value" or "no value" in that context.For an example of where Null in an FK makes sense, how about a standard hierarchy table? You'll have an ID and a parent ID in the table. The parent ID is an FK to another row's ID, in the same table. Traditionally, in this structure, a null parent ID means "no value", which means that row has no parent and is the top of a hierarchy structure.
(Some people don't like that use of null, or don't like that way of defining hierarchies, or both, but it's a simple example of a null FK that makes some sense.)
So, depending on your tables, it might make sense for some rows in the second table to have no reference for the FK.
That's an example where the FK=Null makes a lot of sense but in this particular instance the realtionship isn't a Employee->Supervisor relationship.
I believe I know why they are trying to add this kind of Constraint but I also know they let their rocedural programmers make DB changes without any formally DB trained person at least reviewing them and so they often do not employ the best method for something. In fact I've seen them add constraints only to drop them in the next update/service pack.
Thanks
Thanks
Kindest Regards,
Just say No to Facebook!January 14, 2009 at 1:28 pm
Yeah. The most likely explanation is that the person/people who built it don't understand what they're doing. Very true.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 1:30 pm
Basically allowing a NULL in a Foreign Key is allowing for an optional relationship. For instance in a system I am working on we have a birth_state_id in the persons table, we allow nulls, but we defined the relationship because we only want NULL or a valid state_id. I know some designers would not allow NULL, but would have an Unknown or N/A value for the optional relationship.
IMO, the nasty part of this is the left join it requires.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 14, 2009 at 1:32 pm
Jack Corbett (1/14/2009)
Basically ....IMO, the nasty part of this is the left join it requires.
Exactly. I can almost guarantee with certaint that the same people will have 1 or more pieces of code that are not taking into account thye possible NULL and so another problem/bug will reaer it's ugly head later on after we update.
Thanks
Kindest Regards,
Just say No to Facebook!Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply