18

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.

Improve this question

3 Answers 3

Reset to default
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);
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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: 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).
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)
Improve this answer

Comments

2

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) );
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.