create table date_dimension (
id serial primary key,
date_id date,
..... others
);
create table temp (
id serial primary key,
from_date integer,
to_date integer,
value integer,
foreign key (from_date, to_date) references date_dimension(id, id)
);
How can I refer both from_date and to_date to id field in date_dimension?
The current code fails to do that saying
ERROR: there is no unique constraint matching given keys for referenced table "date_dimension"
Thank you
each FOREIGN KEY constraint added to a table will always relate one row in the referencing table to one row* in the referant. If you want to have each row in the referencing to refer to two distinct rows in the referant, you need two, separate foreign key constraints.
you want:
foreign key (from_date) references date_dimension(id)
foreign key (to_date) references date_dimension(id)
You almost always want to have exactly the rows in the foreign key to be the same as the primary key in the referant.
* Actually, there may be more than one row in the referant if the foreign key is smaller than a candidate key of the referant. this is seldom useful, though, and almost certainly unrelated to the problem you're describing
