Re: Otional FK in Informix?

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. :-) _____________________________________________________________________________________ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.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. Standard disclaimers of me sometimes being delusional apply. Will Sent via Deja.com http://www.deja.com/ Before you buy.