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