Re: Foreign Key with Constant

Why not just put everything into *one* table instead?

create table fk_test_both
(
id integer,
type char
primary key (id,type)
foreign key (id,type) references fk_test_lookup (lu_id,lu_type)
);

You can then make it look like what you planned originally with a couple of views:

create view fk_test_primary as select id as p_id from fk_test_both where type = 'A';

create view fk_test_secondary as select id as s_id from fk_test_both where type = 'O';

create rule insert_primary as on insert to fk_test_primary do instead insert into fk_test_both values (new.*, 'A');
create rule insert_secondary as on insert to fk_test_secondary do instead insert into fk_test_both values (new.*, '0');
create rule update_primary as on update to fk_test_primapry do instead update fk_test_both set id=new.id where id=old.id and type='A';
create rule update_primary as on update to fk_test_primapry do instead update fk_test_both set id=new.id where id=old.id and type='0';

Dima

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)
> );
>
> 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