Nullable Foreign key constriants
in [Programming SQL]
|
From: John Crews on 13 Dec 2009 20:30 Again, thank you for you valuable comments Erland. "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 >
From: John Crews on 13 Dec 2009 20:29 Thank you for your comments Sylvain. "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message news:Oxc12yDfKHA.6000(a)TK2MSFTNGP06.phx.gbl... > 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 >> > >
From: Jay on 13 Dec 2009 21:03 I think that this comment is important enough that it bears repeating. "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message news:Oxc12yDfKHA.6000(a)TK2MSFTNGP06.phx.gbl... > > 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.
From: Jay on 13 Dec 2009 21:23 "David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message news:8t2dnXp07vjCrbjWnZ2dnUVZ8gqdnZ2d(a)giganews.com... 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. > Uh, by definition this would not be true. If you are joining on the foreign key column, it would be for the specific data available in the lookup table for that ONE row. After all, if it's a FK, then it's a PK in the other table. As to the rest, I just disagree.
From: John Crews on 13 Dec 2009 21:50
Thank you for your comments Jay. "Jay" <spam(a)nospam.org> wrote in message news:%23FLmmRGfKHA.3792(a)TK2MSFTNGP02.phx.gbl... > "David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message > news:8t2dnXp07vjCrbjWnZ2dnUVZ8gqdnZ2d(a)giganews.com... > 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. >> > > Uh, by definition this would not be true. If you are joining on the > foreign key column, it would be for the specific data available in the > lookup table for that ONE row. After all, if it's a FK, then it's a PK in > the other table. > > As to the rest, I just disagree. > |