Nullable Foreign key constriants
in [Programming SQL]
|
From: Jay on 20 Dec 2009 23:41 As far as I'm concerned David, you are a purest and don't really know what you're talking about. I have read your posts thinking I may learn something useful - waste of time. If you're that concerned with NULLs, than use the ISNULL function, that's what it's there for. As for me, I'm dropping this thread. "David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message news:N_ednToM6J5_HLPWnZ2dnUVZ8rKdnZ2d(a)giganews.com... > "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 > >
From: David Portas on 21 Dec 2009 06:33 "Jay" <spam(a)nospam.org> wrote in message news:u%23YppffgKHA.2596(a)TK2MSFTNGP04.phx.gbl... > As far as I'm concerned David, you are a purest and don't really know what > you're talking about. I have read your posts thinking I may learn > something useful - waste of time. > > If you're that concerned with NULLs, than use the ISNULL function, that's > what it's there for. > > As for me, I'm dropping this thread. > I don't know what I said that was "purest". I'm trying to talking about practical issues and common sense advice rather than make assertions about what is right or wrong. My advice is to use nulls where they are advantageous for practical reasons of design and coding. Do you disagree and if so what alternative advice do you have to offer? -- David Portas
From: --CELKO-- on 21 Dec 2009 19:19
>> As far as I'm concerned David, you are a purest and don't really know what you're talking about. I have read your posts thinking I may learn something useful - waste of time. << Dave has a pretty good track record and he is absolutely right. Newbies who have no idea about RDBMS blindly make all their columns NULL-able to save typing. They have no idea how to design encoding schemes, nor how to do basic research for standards. The most basic principle of designing with NULLs is that they must have one and only one meaning. I make people put that meaning in a comment in the DDL and document it. I want them to focus on it. Dave has a good way to get this focus. I use questions: 1) Is there an industry standard for unknown or missing values (i.e. ISO sex_code = 0)? 2) Are there more than one kind of unknown or missing values (i.e. all zeroes versus all nines in ICD)? 3) if I let you use a NULL what does it mean? How do you compute with it? What are your predicates going to be like? (i.e. using NULL for end_period_date is a common temporal trick; it must include CHECK (start_period_date <= end_period_date); it means that the period is still active) >> If you're that concerned with NULLs, than use the ISNULL [sic: COALESCE] function, that's what it's there for. << LOL! First of all, you don't know about COALESCE! This is like talking in Hill Billy dialect. And then the content of the sentence is so damn wrong. NULLs affect aggregate math, predicates, host language embeddings, code optimization and a ton of other things. I am writing the fourth edition of SQL FOR SMARTIES and I want to spend more time on NULLs because of the shock that fries the brain of Newbies when they try to understand it. Now I have a great quote from a fried brain :) |