foreign keys with constant part

From: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Subject: foreign keys with constant part
Date: 2012-07-11 10:41:26
Message-ID: C4DAC901169B624F933534A26ED7DF310861B619@JENMAIL01.ad.intershop.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I have to define a foreign relation to something like a unique partial
index.
I could do achieve this with triggers, but I already have too much of
them, which make the model hard to understand.
for now, I will just add a constant column which allows to define a
standard foreign key.

Would an extension of the foreign keys declaration like following make
sense, or does it too much break SQL standards ?

best regards,

Marc Mamin

create temp table t (
a int,
b int,
constraint t_pk primary key (a, b)
);
create unique index t_partial on t (a) where b=5;

create temp table f (
a int
)

alter table f add constraint f_fk FOREIGN KEY ( a, 5 ) REFERENCES t
(a,b); --> t_pk
-- or
alter table f add constraint f_fk FOREIGN KEY ( a ) REFERENCES t (a)
WHERE t.b=5; --> t_partial

for now:

create temp table f (
a int,
five int default 5 -- :-(
)
alter table f add constraint f_fk FOREIGN KEY ( a, five ) REFERENCES t
(a,b);

Browse pgsql-general by date

  From Date Subject
Next Message Jeremaine Johnson 2012-07-11 11:20:36 Fulltext Search Function
Previous Message Toby Corkindale 2012-07-11 07:43:37 Bug? Prepared queries continue to use search_path from their preparation time