Kfir Cohen Kfir Cohen - 1 month ago 7
SQL Question

Trying to pass a FOREIGN KEY of type date is throwing an error

I have created a table like so:

CREATE TABLE festival
(
title text,
place text,
sdate date,
edate date,
PRIMARY KEY (title)
);


Now, I would like to create another table which would use "title" and "edate" from the festival table.

CREATE TABLE event
(
title text,
edate date,
etime time,
FOREIGN KEY (title, edate) REFERENCES festival (title, edate),
PRIMARY KEY (title, edate, etime)
);


I am getting an error:


there is no unique constraint matching given keys for referenced table "festival"


If I only try to pass the title as a foreign key, then it works, however, if I only try to pass the edate as a foreign key, then it fails (meaning the issue is in "edate" for some reason).

Why is this happening?

Answer

A foreign key relationship should link to the primary key of another table. So, you should not have edate in the definition:

FOREIGN KEY (title) REFERENCES festival (title),

There is no reason to repeat edate in the two tables, if they are supposed to be the same. Just keep it in festival and look it up using a join.

I would also recommend that you change festival to be:

CREATE TABLE festival (
    festivalId serial primary key,
    title text unique,
    place text,
    sdate date,
    edate date
);

That is, introduce a synthetic key which can be used for foreign key relationships. Declare title to be unique. And then use festivalId in the events table to get both the title and the date.

Note: This assumes that the primary key on festival should only be on title. If it is on (title, edate), basically the same logic holds. In fact, just introduce festivalId and use that to look up all information about the festival.