From: | "Gregory Wood" <gregw(at)com-stock(dot)com> |
---|---|
To: | "PostgreSQL-General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Foreign Key with Constant |
Date: | 2003-02-21 16:37:17 |
Message-ID: | 068b01c2d9c7$c7df3a90$4f89ffcc@eng3 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Is it possible to create a foreign key that uses a constant for one of the
fields. In other words, I want to foreign key one value into another table,
but only if a constant on the first table matches another column in the
second table.
For example, I want to do something similiar to this:
CREATE TABLE fk_test_lookup
(
LU_ID INTEGER PRIMARY KEY,
LU_Type CHAR
);
INSERT INTO fk_test_lookup (LU_ID,LU_Type) VALUES (1,'A');
INSERT INTO fk_test_lookup (LU_ID,LU_Type) VALUES (2,'A');
INSERT INTO fk_test_lookup (LU_ID,LU_Type) VALUES (3,'O');
CREATE TABLE fk_test_primary
(
P_ID INTEGER PRIMARY KEY,
FOREIGN KEY (P_ID,'A') REFERENCES fk_test_lookup (LU_ID,LU_Type)
);
CREATE TABLE fk_test_secondary
(
S_ID INTEGER PRIMARY KEY,
FOREIGN KEY (S_ID,'O') REFERENCES fk_test_lookup (LU_ID,LU_Type)
);
So that I can only insert P_ID 1 and 2 into fk_test_primary and S_ID 3 into
fk_test_secondary?
I'd rather do this without adding a 'constant' column, or messing too
extensively without triggers, but I fear that one of these might be
necessary.
Thanks,
Greg
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-02-21 16:50:17 | Re: Foreign Key with Constant |
Previous Message | Christopher Murtagh | 2003-02-21 16:27:41 | Re: Slow update - index problem? |