Re: Otional FK in Informix?

From: William Rice <ricew@operamail.com> > >In article <90o0oi$otv$1@news.xmission.com>, > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > > > From: Brett Randall <Brett@reply.to.newsgroup> > > > > > > > Am I alone in thinking this is a bug? I mean, a foreign key field >that > > >gets > > > > inserted without a related primary? > > > > > >Sorry Obnoxio, no support from here. > > > > > >It is perfectly legal to allow a NULL into a field that is a foreign >key > > >(provided it allows nulls of course). Of course there will be no > > >matching NULL in the referenced primary key, because primary key >fields > > >should always be NOT NULL. > > > > > >In fact, NULL is the only value allowed to do this. That's because >NULL > > >is not a value, it indicates the absence of a value. > > > > > >In practical terms, imagine a table colours with PK colour_code, and >a > > >person table with FK hair_colour. The business rule may be that you >can > > >record a new person without knowing their hair_colour. So >hair_colour > > >will allow NULL. This doesen't mean that a "NULL" colour needs to be > > >created in the colour table. > > > > > >If you need a "manditory foreign key", then the foreign key field >must > > >simply be made NOT NULL. > > > > Blimey! Connolly, et al: "If a foreign key exists in a relation, >either the > > foreign key value match the primary key of some tuple in its home >relation > > or the foreign key value must be wholly null." > > > > I don't have a copy of Codd or Date to hand, so I'll have to admit I >was > > wrong. > > > > Ah. A brief search on the net say Date backs me, but Codd backs Will. > > General use also backs Will, so I'll defer on this one. I should read >more > > widely. :-) > > > >If I recall correctly Date also says denormalization for any reason >(including DSS star schema) is an abortion which shouldn't ever be used. I'm with him there too. I've never denormalized in 20-odd years of building systems. _____________________________________________________________________________________ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com