Search code examples
databasepostgresqlforeign-keys

Postgres: two foreign keys to same primary key field

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

Solution

  • 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