Andrus Moor wrote:
> 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.
I believe I get what you want to do - You basically have two (or more)
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