RE: Otional FK in Informix?

I think OC is correct in terms of relational theory But then SQL is not truly *relational* - (Informix is the only vendor doing this). I do know that Date objects to NULLs - so the opportunity would not arise. -----Original Message----- From: Obnoxio The Clown [mailto:obnoxio@hotmail.com] Sent: 07 December 2000 12:08 To: informix-list@iiug.org Subject: 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

OK, I need help now. If Date objects strongly to NULLs, then how does he deal with optional attributes of an entity? The only way I see to achieve this without NULLs is with a 1 to 0-or-1 relation. Is this desirable in the real world? With NULLs CREATE TABLE colours ( colour_code INTEGER NOT NULL PRIMARY KEY, colour_desc CHAR(10) NOT NULL ); CREATE TABLE person ( key SERIAL NOT NULL, name CHAR(50) NOT NULL, hair_colour INTEGER REFERENCES colours(colour_code) ); Without NULLs CREATE TABLE colours ( colour_code INTEGER NOT NULL PRIMARY KEY, colour_desc CHAR(10) NOT NULL ); CREATE TABLE person ( key SERIAL NOT NULL PRIMARY KEY, name CHAR(50) NOT NULL ); CREATE TABLE person_hair ( key INTEGER NOT NULL REFERENCES person(key) PRIMARY KEY, hair_colour INTEGER NOT NULL REFERENCES colours(colour_code) ); If the business rule is that recording hair colour is not required to record a person, then surely allowing the NULL foreign key results in the simplest design. Or is having a "Unknown" hair colour the correct design? Comments please! Brett Randall Robert Stuart wrote: > > I think OC is correct in terms of relational theory > But then SQL is not truly *relational* - (Informix is the only vendor doing > this). > > I do know that Date objects to NULLs - so the opportunity would not arise. > > -----Original Message----- > From: Obnoxio The Clown [mailto:obnoxio@hotmail.com] > Sent: 07 December 2000 12:08 > To: informix-list@iiug.org > Subject: 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