From: | Jan Wieck <JanWieck(at)Yahoo(dot)com> |
---|---|
To: | Gregory Wood <gregw(at)com-stock(dot)com> |
Cc: | PostgreSQL-General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Foreign Key with Constant |
Date: | 2003-02-21 18:15:50 |
Message-ID: | 3E566CD6.53FDC850@Yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Gregory Wood wrote:
>
> 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)
> );
I think if you create one more column P_Type, create a curstom BEFORE
INSERT OR UPDATE trigger that simply sets NEW.P_Type to 'A' and finally
have the foreign key (P_ID, P_Type) reference (LU_ID, LU_Type), it
should pretty much do what you want.
Jan
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #
From | Date | Subject | |
---|---|---|---|
Next Message | Cristian Custodio | 2003-02-21 18:19:43 | Mutating table (urgent) |
Previous Message | Dennis Gearon | 2003-02-21 18:10:29 | transactions |