Re: Otional FK in Informix?

On Thu, 7 Dec 2000, 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? This is one of those slightly debatable areas. If you go along with C J Date's views that NULLs are the work of the devil and should be rigorously exorcised from your database, then the detail table won't accept nulls in the ID column, so the insert will fail on that basis, and the referential integrity constraint won't need to be checked. I have considerable sympathy with this view; I generally will take great pains to avoid needing nulls in a database if I can possibly help it, and doubly so when there is a foreign key involved with the null-accepting column. How should you design this pair of tables to avoid problems? Well, there's a conditional constraint between the two tables, which should actually be modelled by three tables: -- master unchanged CREATE TABLE master ( id INTEGER NOT NULL PRIMARY KEY, val CHAR(10) NOT NULL ); -- detail loses foreign key column CREATE TABLE detail ( key SERIAL NOT NULL, value CHAR(10) ); -- table for cross-referencing master and detail when appropriate CREATE TABLE master_detail ( id INTEGER NOT NULL REFERENCES master(id), key INTEGER NOT NULL REFERENCES detail(key), PRIMARY KEY (id, key) ); INSERT INTO master VALUES(1, "One"); INSERT INTO detail VALUES(27, "Twenty-seven"); INSERT INTO detail VALUES(19, "Nineteen"); INSERT INTO master_detail VALUES(23, 19); -- Fails, ref constraint If we put aside such stringent demands to avoid nulls, then the question is "if there is a null in a column that otherwise contains a valid foreign key value, what should the referential integrity checking do"? There are two options. One would be to insist that there's a row in the master table with a null in its primary key -- but that is horrible and verboten and in any case, one null doesn't equal another null (unless you're doing sorting). The other is to say "well, since there isn't any value in the referencing table (because there's a null in it instead), there isn't any referential integrity constraint to check". That's reasonable; that's what happens. No, I don't think it is a bug in the software, in other words. It is a bug in the database design -- see the redesign -- but not in the DBMS. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"