Re: Otional FK in Informix?

From: Jonathan Leffler <jleffler@informix.com> > >William Rice wrote: > > "LuckeLuke" <nguyenq@supernet.ca> wrote: > > > How can i create an optional Foreign Key in Informix? What i want is >to > > > create a column c1 (allow null) in a table t2 , and if c1 is not null, >it is > > > a foreign key to the parent table t1 (only if not null) . > > > > You do pretty much exactly what you described. > > > > Create t1. > > Create t2 with a nullable c1 which references t1 > > > > Unless I am missing something it acts how you requested. > >Will contradicts Obnoxio, but Will is right this time. > >Using SQLCMD, ESQL/C 9.40.UC2, Foundation 2000 9.21.UC1, Solaris 7: > >+ CREATE TABLE master (id INTEGER NOT NULL PRIMARY KEY, val CHAR(10) NOT >NULL); >+ INSERT INTO master VALUES(1, "One"); >+ CREATE TABLE detail (id INTEGER REFERENCES master(id), KEY SERIAL NOT >NULL, value CHAR(10)); >+ INSERT INTO detail VALUES(NULL, 27, "Twenty-seven"); >+ INSERT INTO detail VALUES(23, 19, "Nineteen"); >SQL -691: Missing key in referenced table for referential constraint >(jleffler.r780_3524). >ISAM -111: ISAM error: no record found. >+ DROP TABLE detail; >+ DROP TABLE master; Am I alone in thinking this is a bug? I mean, a foreign key field that gets inserted without a related primary? _____________________________________________________________________________________ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com

Obnoxio The Clown wrote: > > From: Jonathan Leffler <jleffler@informix.com> > > > >William Rice wrote: > > > "LuckeLuke" <nguyenq@supernet.ca> wrote: > > > > How can i create an optional Foreign Key in Informix? What i want is > >to > > > > create a column c1 (allow null) in a table t2 , and if c1 is not null, > >it is > > > > a foreign key to the parent table t1 (only if not null) . > > > > > > You do pretty much exactly what you described. > > > > > > Create t1. > > > Create t2 with a nullable c1 which references t1 > > > > > > Unless I am missing something it acts how you requested. > > > >Will contradicts Obnoxio, but Will is right this time. > > > >Using SQLCMD, ESQL/C 9.40.UC2, Foundation 2000 9.21.UC1, Solaris 7: > > > >+ CREATE TABLE master (id INTEGER NOT NULL PRIMARY KEY, val CHAR(10) NOT > >NULL); > >+ INSERT INTO master VALUES(1, "One"); > >+ CREATE TABLE detail (id INTEGER REFERENCES master(id), KEY SERIAL NOT > >NULL, value CHAR(10)); > >+ INSERT INTO detail VALUES(NULL, 27, "Twenty-seven"); > >+ INSERT INTO detail VALUES(23, 19, "Nineteen"); > >SQL -691: Missing key in referenced table for referential constraint > >(jleffler.r780_3524). > >ISAM -111: ISAM error: no record found. > >+ DROP TABLE detail; > >+ DROP TABLE master; > > 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. Regards Brett Randall