Re: Foreign Key with Constant

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@Yahoo.com #