No unique constraint matching given keys for referenced table

Question

I have a date_dimension table definition:

CREATE TABLE date_dimension
(
  id integer primary key,
  date text,
  year double precision,
  year_for_week double precision,
  quarter double precision
);

I am trying to create a fact table that fails

create table fact ( 
  id serial primary key,
  contract integer,
  component integer,
  evaluation_date integer,
  effective_date integer,
  foreign key (evaluation_date, effective_date) references date_dimension(id, id)
);

The error is :

ERROR:  there is no unique constraint matching given keys for referenced 
table "date_dimension"

SQL state: 42830

I am not sure how to fix this.

Jonathan Hall · Answer 1 · 2015-08-31T17:38:03.767

15

The error tells you the problem: You don't have a unique constraint on date_dimension that matches your foreign key constraint.

However, this leads to the bigger design problem: Your foreign key relationship doesn't make any sense.

You could possibly solve your "problem" with:

CREATE UNIQUE INDEX date_dimension(id,id);

But that's dumb, because id is always the same. It could also be expressed as:

FOREIGN KEY (evaluation_date) REFERENCES date_dimension(id);

Then getting rid of the effective_date column, which would always be identical to evaluation_date in your example.

Or... you probably really want two FK relationships:

FOREIGN KEY (evaluation_date) REFERENCES date_dimension(id);
FOREIGN KEY (effective_date) REFERENCES date_dimension(id);

edited Aug 31 '15 at 17:38

answered Nov 29 '11 at 20:08

Jonathan Hall

75,165
16
143
189

I have this error, and I can't make a constraint because we have duplicate rows in that table. Obviously Foreign Key is not what I want, but I'd like a constraint: "exists in this table", is that a possibility? – isaaclw Apr 15 '15 at 22:47
1

@isaaclw: There are ways to do that, but they usually aren't very pretty. You should ask another question (or search--I'm sure someone has already asked it here). – Jonathan Hall Apr 15 '15 at 23:29

score 10 · Accepted Answer · answered Nov 29 '11 at 20:08

10

I think you are looking for two separate foreign keys:

foreign key (evaluation_date) references date_dimension(id),
foreign key (effective_date) references date_dimension(id)

answered Nov 29 '11 at 20:08

Mark Byers

811,555
193
1,581
1,452

score 2 · Answer 3 · answered Nov 29 '11 at 20:15

Don't you just want to create two separate foreign key references to the date dimension as follows:

create table fact ( 
    id serial primary key,
    contract integer,
    component integer,
    evaluation_date integer,
    effective_date integer,
    foreign key (evaluation_date) references date_dimension(id),
    foreign key (effective_date) references date_dimension(id)
);

No unique constraint matching given keys for referenced table

3 Answers3

Linked