I believe I get what you want to do - You basically have two (or more)thank you for reply. There was a typo in my code. Second table should be
CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), FOREIGN KEY ('1', code1) REFERENCES classifier, FOREIGN KEY ('2', code2) REFERENCES classifier );
I try to explain my problem more precicely.
I can implement the referential integrity which I need in the following way:
CREATE TABLE classifier ( category CHAR(1), code CHAR(10), PRIMARY KEY (category,code) );
CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), constant1 CHAR default '1', constant2 CHAR default '2', FOREIGN KEY (constant1, code1) REFERENCES classifier, FOREIGN KEY (constant2, code2) REFERENCES classifier );
This implementation requires 2 additional columns (constant1 and constant2) which have always same values, '1' and '2' respectively, in all info table rows.
typs of codes, and thereforce your primary key on "classifier" is (category, code). So far, this is basic database design, and your solution is fine.
But now, you need to reference one type-1, and one type-2 code from the "info" table. Now is is pretty non-standard (And, btw, it violates the
0-1-infinity rule, which says that you shouldn't introduce any abitrary limits other than zero or one).
I believe you have two options. Either you keep your "dummy" columns - which are not dummy columns at all, if you name them "category1" and "category2". Then you just have two references to the "classifier" table, each consiting of a "category" and a "code" - which is fine, since this matches the primary key on "classifier".
Or you create a classifier_1 and a classifier_2 table, each containing only the column "code". Then you can drop the "category1" and "category2" fields from "info", and just point the foreign keys to the correct table.
You can, optionally, create a view "classifer", that combiney both classifier_? tables - e.g, do
create view classifier as select '1'::char(1) as category, code from classifier_1 union select '2'::char(1) as category, code from classifier_2 ;
greetings, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
