Nullable Foreign key constriants
in [Programming SQL]
|
From: Erland Sommarskog on 15 Dec 2009 17:57 David Portas (REMOVE_BEFORE_REPLYING_dportas(a)acm.org) writes: > I never suggested using "bogus" data. If the fact represented by a row > does not have some particular attribute then it should be represented in > a table that does not have that attribute. Your argument was: > 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. But this does not get any different, if you put the optional attribute in a table on the side. The error you describe is not with NULLs, it's an error of people who fail to consider than an optional attribute is exactly that. An error which is most easy to make with attributes that are only missing for a smaller number of the instances. And while you can sweep the NULLs under the data-model carpet, they will pop up first thing you run an outer join. -- 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 19 Dec 2009 13:43 "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CE2F3BB3E764Yazorman(a)127.0.0.1... > David Portas (REMOVE_BEFORE_REPLYING_dportas(a)acm.org) writes: >> I never suggested using "bogus" data. If the fact represented by a row >> does not have some particular attribute then it should be represented in >> a table that does not have that attribute. > > Your argument was: > >> 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. > > But this does not get any different, if you put the optional attribute > in a table on the side. The error you describe is not with NULLs, it's > an error of people who fail to consider than an optional attribute is > exactly that. An error which is most easy to make with attributes that > are only missing for a smaller number of the instances. > > And while you can sweep the NULLs under the data-model carpet, they > will pop up first thing you run an outer join. Good point: Null is about the absence of a piece of data or the absence of knowledge about it. However, this is also the same kind of thing with an Outer Join: missing or absence of data. So people who don't want to have Null values in their databases - and especially nullable foreign keys - should also forbid themselves to use any kind of Outer Join under any circonstance. You cannot reject something from one hand and take it back with another. -- 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)
From: Sylvain Lafontaine on 19 Dec 2009 13:54 "David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message news:BcSdnUVldJN9XbvWnZ2dnUVZ8tydnZ2d(a)giganews.com... > When you say that the design without nulls is "more complicated" I > understand you to mean that by making a column nullable you can "simplify" > the design by removing some other column or table. That alone is not a > very sufficient argument however. ==> If reducing the number of tables and > columns was in itself a reason to use null then the conclusion would be to > have just one table in every database and make every non-key column in > that table nullable. I'm sure you don't advocate that. ... Funny enough, having only one single table and the relationships between your data materialized by the queries that you are making against it - instead of defining it using tables and specific relationships - is the latest trend in databases. They are two different database platform in the Window Azure Platform and the first one is exactly like that. As the second, called the SQL Azure database, while externally it might ressemble an ordinary SQL-Server database, I've heard that internally, it's simply an extension of the other one. The REST protocol used for accessing the Window Azure Platform is also a direct application of that principle. -- 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)
From: David Portas on 20 Dec 2009 14:38 "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message news:OtXXCzNgKHA.3792(a)TK2MSFTNGP02.phx.gbl... > > Funny enough, having only one single table and the relationships between > your data materialized by the queries that you are making against it - > instead of defining it using tables and specific relationships - is the > latest trend in databases. > > They are two different database platform in the Window Azure Platform and > the first one is exactly like that. As the second, called the SQL Azure > database, while externally it might ressemble an ordinary SQL-Server > database, I've heard that internally, it's simply an extension of the > other one. > SQL Azure is basically SQL Server running on a virtual machine. The internals don't dictate the logical design of the database. The relational model and for that matter the ISO SQL standard are concerned only with the external logical interface of the DBMS, completely independently of how that is achieved internally. You still need to define a data model in SQL Azure and therefore as a database designer it is still your responsibility to decide when and when not to use nulls. -- David Portas
From: David Portas on 20 Dec 2009 14:58
"Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message news:O$enqsNgKHA.1112(a)TK2MSFTNGP04.phx.gbl... > > Good point: Null is about the absence of a piece of data or the absence of > knowledge about it. However, this is also the same kind of thing with an > Outer Join: missing or absence of data. So people who don't want to have > Null values in their databases - and especially nullable foreign keys - > should also forbid themselves to use any kind of Outer Join under any > circonstance. You cannot reject something from one hand and take it back > with another. > I for one do not "reject" nulls. I merely said that it's important to separate the use of nulls from any business concerns. The decision to use a null or not rests entirely with the database designer and he or she cannot and should not dodge that responsibility by claiming that the business needed it all along. In some SQL shops I have known it is common practice to avoid nulls in table and view design. It is much harder to avoid them in SQL queries however because SQL creates nulls in all sorts of places - outer joins being only one example, SUM() of an empty set being another. The database designer cannot always control every query that will be written against his database. However, nulls are seldom what is truly required of any query that returns data to a user. Developers writing reports or screens usually have to spend time hiding or re-presenting nulls (as zeros or empty strings for example). If SQL had better support for supplying alternative values in an outer join then database developers would surely take advantage of that to avoid nulls before the data got into the hands of data consumers. -- David Portas |