Thread: Foreign Key with Constant

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

On Fri, 21 Feb 2003, 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,

Not directly no.

> 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.

Unfortunately I think you'll need to do either the constant column or
triggers (the SQL mandated way of solving this is probably check
constraints with subselects but we don't support that)  If you only
care about insert time on the fk table and not about moidfications to the
pk table you can get away with a simple function that you use in a check
constraint I think.

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 #

> > 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,
>
> Not directly no.

*nod* I figured as much.

> > 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.
>
> Unfortunately I think you'll need to do either the constant column or
> triggers (the SQL mandated way of solving this is probably check
> constraints with subselects but we don't support that)  If you only
> care about insert time on the fk table and not about moidfications to the
> pk table you can get away with a simple function that you use in a check
> constraint I think.

Actually, I think that sounds like the best solution. It will be a
relatively infrequently used table and I'd like to keep things simple (not
cluttering up the design with extra columns and triggers).

Thanks!

Greg

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.idand 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.idand 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