Nullable Foreign key constriants
in [Programming SQL]
|
From: John Crews on 13 Dec 2009 09:11 I did go to books online from one of the links you provided. The below is an excerpt from SQL Server 2008 Books online: "Achieving a Well-Designed Database" A table should avoid nullable columns. Tables can have columns defined to allow for null values. A null value indicates that there is no value. Although it can be useful to allow for null values in isolated cases, you should use them sparingly. This is because they require special handling that increases the complexity of data operations. If you have a table with several nullable columns and several of the rows have null values in the columns, you should consider putting these columns in another table linked to the primary table. By storing the data in two separate tables, the primary table can be simple in design and still handle the occasional need for storing this information. ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/e023304d-3d0a-4cd2-ac58-e9ea0415c114.htm So, for the example I gave, wouldn't having 5 or more nullable fields be contrary to the above excerpt from SQL Server 2008 Books Online? Again, thank you for your comments. "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 >
From: David Portas on 13 Dec 2009 13:20 "John Crews" <trex3648(a)hotmail.com> wrote in message news:%23FtdFA6eKHA.4112(a)TK2MSFTNGP06.phx.gbl... >I am just trying to get a general consenus from SQL experts on whether or >not nullable Foreign Key contrainsts are a key database design or a poor >database design. Comments and opinions on this is appreciatable. > > Thanks, > Minimising use of nulls is often good advice because they frequently add complexity to queries and other logic. The fact that a column is subject to a foreign key constraint is at least one potential reason to avoid using null. Users and developers may automatically assume they can join on foreign key columns and get a result that includes every row in the joined tables. I do have to disagree with the statement in this thread that you should use nulls "when the business model requires". No business model "requires" a null because there are no nulls in the real world that the business model relates to. Nulls are an abstraction that database designers use to suit some technical purpose in the database design or code. That is all. Whether or not you choose to use them I think you should keep that point in mind. If you do make a column nullable, just make sure you understand and can justify why you are adding nulls in that place. -- David Portas
From: John Crews on 13 Dec 2009 13:37 Thank you David for your comments. "David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message news:8t2dnXp07vjCrbjWnZ2dnUVZ8gqdnZ2d(a)giganews.com... > "John Crews" <trex3648(a)hotmail.com> wrote in message > news:%23FtdFA6eKHA.4112(a)TK2MSFTNGP06.phx.gbl... >>I am just trying to get a general consenus from SQL experts on whether or >>not nullable Foreign Key contrainsts are a key database design or a poor >>database design. Comments and opinions on this is appreciatable. >> >> Thanks, >> > > Minimising use of nulls is often good advice because they frequently add > complexity to queries and other logic. The fact that a column is subject > to a foreign key constraint is at least one potential reason to avoid > using null. Users and developers may automatically assume they can join on > foreign key columns and get a result that includes every row in the joined > tables. > > I do have to disagree with the statement in this thread that you should > use nulls "when the business model requires". No business model "requires" > a null because there are no nulls in the real world that the business > model relates to. Nulls are an abstraction that database designers use to > suit some technical purpose in the database design or code. That is all. > Whether or not you choose to use them I think you should keep that point > in mind. If you do make a column nullable, just make sure you understand > and can justify why you are adding nulls in that place. > > -- > David Portas > >
From: Erland Sommarskog on 13 Dec 2009 15:53 John Crews (trex3648(a)hotmail.com) writes: > I did go to books online from one of the links you provided. These links are part of my signature and appear in all my posts in SQL Server-related newsgroups. > Tables can have columns defined to allow for null values. A null value > indicates that there is no value. Although it can be useful to allow for > null values in isolated cases, you should use them sparingly. This is > because they require special handling that increases the complexity of > data operations. If you have a table with several nullable columns and > several of the rows have null values in the columns, you should consider > putting these columns in another table linked to the primary table. By > storing the data in two separate tables, the primary table can be simple > in design and still handle the occasional need for storing this > information. Writing about NULLs is a delicate topic, and Books Online is not really the best read or write about something on which are conflicting opinions also among experts. I have seen horror examples of tables with a single IDENTITY column and all other columns nullable. That is very rarely a correct table design. But that does not mean that are NULL columns are evil. > So, for the example I gave, wouldn't having 5 or more nullable fields be > contrary to the above excerpt from SQL Server 2008 Books Online? Maybe. But I and several others how have posted here have our fair share of practical database design, know that NULLs comes in very handy. -- 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: Sylvain Lafontaine on 13 Dec 2009 16:39
To that I would like to add the point that invoking the increasing complexity of data operations when using Null values is not a valid argument to me: it's clear that if you have a business case that is more complex than another one, then it's data operation will also be more complex. If in one case, you allow for multiphe phone numbers but in another, you allow for only one single phone number; the second case will always be simpler than the first one but this doesn't mean that the first business case is wrong, it simply means that it's more complex. The same hold with Nullable Foreign Key: if you have to use them in a business case because it's a requirement; then it's not a surprise that manipulating the data for this case will be more complex than for another one where it's not a requirement. All other solutions that I've seen so far are "fixes" that are always much worse than the *problem* they are trying to correct in the first place. For example, the simple fact of saying that having bogus data all over your database is better than having null values speaks by itself. Finally, one last point if the documentation feature of using Nullable Foreign Keys in comparaison to other solutions: if a programmer knows nothing about your database and take a first look at it; seeing a nullable foreign key will tell him about the nature of this foreign key relationship. However, if you have chosen to use a bogus data to represent a missing value; he will have to take a deep look before understanding what's going on and if he misses one; you might end up with a wrong result for say a report or with invalid data all over your database. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CE0DEA906363Yazorman(a)127.0.0.1... > John Crews (trex3648(a)hotmail.com) writes: >> I did go to books online from one of the links you provided. > > These links are part of my signature and appear in all my posts in > SQL Server-related newsgroups. > >> Tables can have columns defined to allow for null values. A null value >> indicates that there is no value. Although it can be useful to allow for >> null values in isolated cases, you should use them sparingly. This is >> because they require special handling that increases the complexity of >> data operations. If you have a table with several nullable columns and >> several of the rows have null values in the columns, you should consider >> putting these columns in another table linked to the primary table. By >> storing the data in two separate tables, the primary table can be simple >> in design and still handle the occasional need for storing this >> information. > > Writing about NULLs is a delicate topic, and Books Online is not really > the best read or write about something on which are conflicting opinions > also among experts. > > I have seen horror examples of tables with a single IDENTITY column > and all other columns nullable. That is very rarely a correct table > design. > > But that does not mean that are NULL columns are evil. > >> So, for the example I gave, wouldn't having 5 or more nullable fields be >> contrary to the above excerpt from SQL Server 2008 Books Online? > > Maybe. But I and several others how have posted here have our fair > share of practical database design, know that NULLs comes in very > handy. > > -- > 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 > |