Re: [Q] FOREIGN AND PRIMARY KEY SYNTAX PROBLEMS

}From: abethelm@ccssrv1.corp.harris.com (Ancil Abethelmy) }Subject: [Q] FOREIGN AND PRIMARY KEY SYNTAX PROBLEMS }Date: Fri, 29 Jul 1994 19:24:21 GMT }X-Informix-List-Id: <news.7919> } }I am having problems creating tables with FOREIGN KEYS } }I may be too close to the problem so can't see something really simple but }If you know what is wrong with a 4.01 i-sql, 5.01 online on SCO UNIX 3.2 and this syntax }please let know: } }CREATE TABLE test } (test_cd char(3) NOT NULL, } test_ds char(30) NOT NULL } , PRIMARY KEY (test_cd)); This is fine, though I don't think the layout follows any systematic pattern. Nor do I see any system in the next table definition. I had to reformat the code to see what the problem was. Maybe you'd have found it easier to spot the trouble if you had reformatted the code more consistently. }CREATE TABLE testpl } (test_cd char(3) NOT NULL, } testpl_nr char(10) NOT NULL, } testpl_typ_cd char(5) NOT NULL, } testpl_cls_cd char(2) NOT NULL, } testpl_ds char(30), } testpl_note_tx varchar(10000) } , PRIMARY KEY (test_cd, } testpl_nr, } testpl_typ_cd) } , FOREIGN KEY fk_test (test_cd } ) REFERENCES test } ); This is not how you name foreign keys. If you want to name the foreign key, you have to use the notation: FOREIGN KEY (test_cd) REFERENCES test CONSTRAINT fk_test; I name all my primary key and foreign key constraints, but I use the name of the referencing (rather than referenced) table in the constraint name, so I'd write: FOREIGN KEY (test_cd) REFERENCES test CONSTRAINT fk_testpl; Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>