From: | Dmitry Tkach <dmitry(at)openratings(dot)com> |
---|---|
To: | Gregory Wood <gregw(at)com-stock(dot)com> |
Subject: | Re: Foreign Key with Constant |
Date: | 2003-02-22 02:43:59 |
Message-ID: | 3E56E3EF.2060509@openratings.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-02-22 02:51:07 | Re: Views + Rules + Triggers + 7.3.2 = Upgrade Problems (was: Re: REWRITE_INVOKE_MAX and "query may contain cycles" ) |
Previous Message | Tom Lane | 2003-02-22 02:33:21 | Re: A problem with sequences... |