Nullable Foreign key constriants
in [Programming SQL]
|
From: Hugo Kornelis on 13 Dec 2009 06:52 On Sun, 13 Dec 2009 00:18:00 -0500, John Crews wrote: (snip) >So, for the above business model, am I being stubborn by insisting that our >data model does not include nullable FK constraints or is not allowing >nullable FK constraints a good database design practice? Hi John, The short answer is yes, you are being stubborn. Nullability and foreign keys are two disstinct constraints, that are not as related as the question implies. You use a NOT NULL constraint if for each row, a value must appear in a column. You use a FOREIGN KEY constraint if each value in a column must be taken from a key value in the referenced table. All four combination of presence/absence of these constraint types are expected to be in any database that is large enough (some are more common than others, though). I have *some* understanding for people who think that nulll values should not be allowed in a relational database. I disagree with them, but they do have a foundation for theiir ideas. This will of course cause problems when the business models calls for allowing some values (whether attributes or 1-to-n relationships is not really important for this) to be absent when entering a row. The "solutions" offered fall apart in two categories: 1) Use special values instead. So you avoid the pre-programmed, tried and tested special value NULL, and isntead use your own, forcing you to write special-case handling for these special values when using the column in joins and aggregates - special-case handling that comes standard when you use NULL. 2) Split optional attributes and relationships in their own tables. SO if birthdate is not known for every person, you'd create an extra table "PersonsWithBirthdate" (okay, there might be btter names) with the PersonID column as primary key and foreign key into the Persons table, and BirthDate as a not nullable second column. This effectively removes NULLs from the table design, but at the price of getting much more tables (especially when many columns are optional) - and you'll still get NULLs in result sets, becasuse you'll have to use OUTER JOIN for many queries. So if you want to build a data model that is completely NULL free, there are options - but my advice would be to accept that the real world has business requirements where data is sometimes missing, and accept that while NULL might not be the 100% ideal way to represent this, it does get a lot closer to the 100% mark than any of the available alternatives (in SQL Server that is - I've heard of products designed specifically to handle relational data without NULLs, but have never investigated them). And if you want to allow NULLs in columns that are not part of a foreign key but disallow then in columns that are, I'd have to ask you why you think that foreign key columns should be treated differently WRT nullability than non-foreign key columns. -- Hugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Gert-Jan Strik on 13 Dec 2009 06:54 John, In my opinion the relevance of the Foreign Key constraint is not that important in the decision. All experts will answer your question with "it depends". I think it depends on the business rules and what makes sense. If a particular piece of information must be available for the object (row entry) to make sense, then it should not be nullable. If it is optional (from a business point of view), then you have a choice: make the column NULLable, or use a dedicated value to describe the specific situation (in both the referencing and referenced table). Where it does not depend on, is the order in which you process thing. It is common to make the mistake of creating an "empty" row as a sort of placeholder for all the data that is to follow. IMO, that is asking for problems, and I think you disallow such situations. So to react to your example, if your table is supposed to have values for each of these 5 columns (with foreign keys), then it doesn't make sense to make them NULLable. You asked the question: is not allowing nullable FK constraints a good database design practice? I would say no, because it misses the point. I would say that you should identify which column values are required, and which are not. For the ones that are not required, you will have to make a choice: allow a NULL (and make sure everyone understands what it means, which might depend on why the information is not required) or use a magic number (and make sure this actually has a meaning, and is not just a substitution for NULL). -- Gert-Jan SQL Server MVP John Crews wrote: > > Celko, > > Thank you for reply. > > I was looking for a answer on general database design. If I were creating a > database from the start, would it be a good design pattern to have nullable > Foreign Key constraints or would it be a poor design pattern to have > nullable Foreign Key constraints. However, I will attempt to illistrate an > example with out having to layout the entire business requirements of the > system I am designing. I would also like to point out that I have the > opinion that nullable FK are the exception and not the rule and should be > avoided if at all possible. I ask this only because my opinion is being > challenged and I would like a consensus if I am just being hard-headed or am > I generally correct on this concept. > > Lets say, for example, I have a table with the a PK, some attributes, and > then five FK constraints to five look up tables. Upon creation of the > record for this table none, one, or all of the FK values to the look up > tables are known. Therefore, the potential to have a record with five null > values is very likely. One solution is to have these FK attributes nullable > (admittedly, this is most likely the easiest solution). Another solution is > to have a record in each of the five look up tables with a value something > similar to 'Not Known' (this seems to me to be the correct solution). Other > solutions could be to remove the FK constriant, have the fields still be > nullable, and enforce the RI via an Update or Instead of trigger or in the > business layer code (Ouch....I am not a fan of any nullable attribute). > Still, another possibility is to create five one-to-one tables with each > having a FK constraint to their respective look up table (this would be my > second choice). When the values for the look up tables are known for this > particular entity then a record is created in the respective one-to-one > table. > > So, for the above business model, am I being stubborn by insisting that our > data model does not include nullable FK constraints or is not allowing > nullable FK constraints a good database design practice? > > thanks, > > "--CELKO--" <jcelko212(a)earthlink.net> wrote in message > news:54da716a-bebc-4b9a-b277-6d67d8375df0(a)e27g2000yqd.googlegroups.com... > >>> I am just trying to get a general consenus from SQL experts on whether > >>> or not nullable Foreign Key constraints are a key database design or a > >>> poor database design.<< > > > > A very general question gets a very general answer :) > > > > I would prefer to use special values in the domain of the data > > elements involved. Remember good old "Professor TBD" (To Be > > Determined) when they did not have a professor for a class yet? This > > lets you have several special values for different situations such > > as"un-diagnosed" versus "diagnosed, unknown disease" -- very different > > kinds of missing data! > > > > I have a client who uses DUNS for his suppliers; if a new guy has no > > gotten one yet, then they use "Xdddddddd" until he does get one. > > > > But the real answer is that it depends on the data model.
From: Erland Sommarskog on 13 Dec 2009 07:03 John Crews (trex3648(a)hotmail.com) writes: > I would also like to point out that I have the opinion that nullable FK > are the exception and not the rule and should be avoided if at all > possible. I ask this only because my opinion is being challenged and I > would like a consensus if I am just being hard-headed or am I generally > correct on this concept. I would agree with that challenge. > Lets say, for example, I have a table with the a PK, some attributes, > and then five FK constraints to five look up tables. Upon creation of > the record for this table none, one, or all of the FK values to the look > up tables are known. Therefore, the potential to have a record with > five null values is very likely. One solution is to have these FK > attributes nullable (admittedly, this is most likely the easiest > solution). And in most cases the only reasonable. > Another solution is to have a record in each of the five look up tables > with a value something similar to 'Not Known' (this seems to me to be > the correct solution). I disagree. Take the example that Tom gave. To implement ReferalCustID you would need a "Unknown customer" in your customer table. Since there are probably a lot of columns in you customer table that are not nullable, you would also have to make up a lot of bogus data for this bogus customer. Not a big deal, but what is more troublesome is that you now need to add logic to make sure that this unknown customer does not end up in the Orders table, where it would constitute a business-rules violation. You would have to add a trigger or a check constraint. The latter would assume that the id of the unknown customer would be hardcoded, for instance -1. What have you achieved by this? You have implemented NULL values with -1 instead of NULL for a more complex and more fragile solution. > Other solutions could be to remove the FK constriant, have the fields > still be nullable, and enforce the RI via an Update or Instead of > trigger or in the business layer code (Ouch....I am not a fan of any > nullable attribute). Certainly poorer choices. Triggers are more complex, and doing validation outside the database sets integrity at stake. > Still, another possibility is to create five one-to-one tables with each > having a FK constraint to their respective look up table (this would be my > second choice). When the values for the look up tables are known for this > particular entity then a record is created in the respective one-to-one > table. There are situations where this solution makes sense. Say that you have a large table, and there are a suite of columns that apply only to a smaller subset of the rows. Then it can make sense to move these to a separate table, to conserve space, and maybe also get them out of the way, to help developers and support staff more easily to digest the table. But in most cases, this only serves to make the data model more complex and more difficult to understand and program against. And in any case, the NULL values will come back when you outer-join with the subtables. > So, for the above business model, am I being stubborn by insisting that > our data model does not include nullable FK constraints or is not > allowing nullable FK constraints a good database design practice? Nullable FK columns are essential components in database design. If you disallow them, you are throwing out the baby with the bathtub. That does not mean to say that every FK column should be nullable, or that every column should be nullable. For every column you add to a table, and which you consider to make nullable, you need to consider what NULL would mean for that particular column, and how you plan to handle it in your application. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: John Crews on 13 Dec 2009 08:38 Thank you for your comments Gert-Jan. "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message news:4B24D60D.43D7D569(a)xs4all.nl... > John, > > In my opinion the relevance of the Foreign Key constraint is not that > important in the decision. All experts will answer your question with > "it depends". I think it depends on the business rules and what makes > sense. If a particular piece of information must be available for the > object (row entry) to make sense, then it should not be nullable. If it > is optional (from a business point of view), then you have a choice: > make the column NULLable, or use a dedicated value to describe the > specific situation (in both the referencing and referenced table). > > Where it does not depend on, is the order in which you process thing. It > is common to make the mistake of creating an "empty" row as a sort of > placeholder for all the data that is to follow. IMO, that is asking for > problems, and I think you disallow such situations. So to react to your > example, if your table is supposed to have values for each of these 5 > columns (with foreign keys), then it doesn't make sense to make them > NULLable. > > You asked the question: is not allowing nullable FK constraints a good > database design practice? > > I would say no, because it misses the point. I would say that you should > identify which column values are required, and which are not. For the > ones that are not required, you will have to make a choice: allow a NULL > (and make sure everyone understands what it means, which might depend on > why the information is not required) or use a magic number (and make > sure this actually has a meaning, and is not just a substitution for > NULL). > > -- > Gert-Jan > SQL Server MVP > > > John Crews wrote: >> >> Celko, >> >> Thank you for reply. >> >> I was looking for a answer on general database design. If I were >> creating a >> database from the start, would it be a good design pattern to have >> nullable >> Foreign Key constraints or would it be a poor design pattern to have >> nullable Foreign Key constraints. However, I will attempt to illistrate >> an >> example with out having to layout the entire business requirements of the >> system I am designing. I would also like to point out that I have the >> opinion that nullable FK are the exception and not the rule and should be >> avoided if at all possible. I ask this only because my opinion is being >> challenged and I would like a consensus if I am just being hard-headed or >> am >> I generally correct on this concept. >> >> Lets say, for example, I have a table with the a PK, some attributes, and >> then five FK constraints to five look up tables. Upon creation of the >> record for this table none, one, or all of the FK values to the look up >> tables are known. Therefore, the potential to have a record with five >> null >> values is very likely. One solution is to have these FK attributes >> nullable >> (admittedly, this is most likely the easiest solution). Another solution >> is >> to have a record in each of the five look up tables with a value >> something >> similar to 'Not Known' (this seems to me to be the correct solution). >> Other >> solutions could be to remove the FK constriant, have the fields still be >> nullable, and enforce the RI via an Update or Instead of trigger or in >> the >> business layer code (Ouch....I am not a fan of any nullable attribute). >> Still, another possibility is to create five one-to-one tables with each >> having a FK constraint to their respective look up table (this would be >> my >> second choice). When the values for the look up tables are known for >> this >> particular entity then a record is created in the respective one-to-one >> table. >> >> So, for the above business model, am I being stubborn by insisting that >> our >> data model does not include nullable FK constraints or is not allowing >> nullable FK constraints a good database design practice? >> >> thanks, >> >> "--CELKO--" <jcelko212(a)earthlink.net> wrote in message >> news:54da716a-bebc-4b9a-b277-6d67d8375df0(a)e27g2000yqd.googlegroups.com... >> >>> I am just trying to get a general consenus from SQL experts on >> >>> whether >> >>> or not nullable Foreign Key constraints are a key database design or >> >>> a >> >>> poor database design.<< >> > >> > A very general question gets a very general answer :) >> > >> > I would prefer to use special values in the domain of the data >> > elements involved. Remember good old "Professor TBD" (To Be >> > Determined) when they did not have a professor for a class yet? This >> > lets you have several special values for different situations such >> > as"un-diagnosed" versus "diagnosed, unknown disease" -- very different >> > kinds of missing data! >> > >> > I have a client who uses DUNS for his suppliers; if a new guy has no >> > gotten one yet, then they use "Xdddddddd" until he does get one. >> > >> > But the real answer is that it depends on the data model.
From: John Crews on 13 Dec 2009 08:40
Thank you for your comments Erland and the links. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CE084CF3B1F8Yazorman(a)127.0.0.1... > John Crews (trex3648(a)hotmail.com) writes: >> I would also like to point out that I have the opinion that nullable FK >> are the exception and not the rule and should be avoided if at all >> possible. I ask this only because my opinion is being challenged and I >> would like a consensus if I am just being hard-headed or am I generally >> correct on this concept. > > I would agree with that challenge. > >> Lets say, for example, I have a table with the a PK, some attributes, >> and then five FK constraints to five look up tables. Upon creation of >> the record for this table none, one, or all of the FK values to the look >> up tables are known. Therefore, the potential to have a record with >> five null values is very likely. One solution is to have these FK >> attributes nullable (admittedly, this is most likely the easiest >> solution). > > And in most cases the only reasonable. > >> Another solution is to have a record in each of the five look up tables >> with a value something similar to 'Not Known' (this seems to me to be >> the correct solution). > > I disagree. Take the example that Tom gave. To implement ReferalCustID > you would need a "Unknown customer" in your customer table. Since there > are probably a lot of columns in you customer table that are not > nullable, you would also have to make up a lot of bogus data for this > bogus customer. Not a big deal, but what is more troublesome is that > you now need to add logic to make sure that this unknown customer does > not end up in the Orders table, where it would constitute a business-rules > violation. You would have to add a trigger or a check constraint. The > latter would assume that the id of the unknown customer would be > hardcoded, for instance -1. > > What have you achieved by this? You have implemented NULL values with > -1 instead of NULL for a more complex and more fragile solution. > >> Other solutions could be to remove the FK constriant, have the fields >> still be nullable, and enforce the RI via an Update or Instead of >> trigger or in the business layer code (Ouch....I am not a fan of any >> nullable attribute). > > Certainly poorer choices. Triggers are more complex, and doing validation > outside the database sets integrity at stake. > >> Still, another possibility is to create five one-to-one tables with each >> having a FK constraint to their respective look up table (this would be >> my >> second choice). When the values for the look up tables are known for >> this >> particular entity then a record is created in the respective one-to-one >> table. > > There are situations where this solution makes sense. Say that you have > a large table, and there are a suite of columns that apply only to a > smaller subset of the rows. Then it can make sense to move these to a > separate table, to conserve space, and maybe also get them out of the > way, to help developers and support staff more easily to digest the table. > > But in most cases, this only serves to make the data model more complex > and more difficult to understand and program against. > > And in any case, the NULL values will come back when you outer-join > with the subtables. > >> So, for the above business model, am I being stubborn by insisting that >> our data model does not include nullable FK constraints or is not >> allowing nullable FK constraints a good database design practice? > > Nullable FK columns are essential components in database design. > If you disallow them, you are throwing out the baby with the bathtub. > > That does not mean to say that every FK column should be nullable, or > that every column should be nullable. For every column you add to a table, > and which you consider to make nullable, you need to consider what > NULL would mean for that particular column, and how you plan to handle > it in your application. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > |