Otional FK in Informix?

Hi , 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) . Thanks

In article <djqX5.9418$7.348229@quark.idirect.com>, "LuckeLuke" <nguyenq@supernet.ca> wrote: > Hi , > 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) . > Thanks > > 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. Hope this helps Will Sent via Deja.com http://www.deja.com/ Before you buy.

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; -- 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!"