I am Creating a movie and tv show streaming service database(for school project) using Postgres 9.6.2. I am running into the following error:
there is no unique constraint matching given keys for referenced table "watchedepisodes"
The TVratings table below will take a tv show, as long as a user has watched at least one episode (that shows up in the WatchedEpisodes table) and allow the user to rate it. Since WatchedEpisodes has a composite primary key of the user id, tv show id, season, and episode, it won't let me just reference from that table a composite key of just uid and tid.
CREATE TABLE WatchedEpisodes (
uid int unique references Users(uid),
tid int,
season int,
episode int,
FOREIGN KEY(tid, season, episode) REFERENCES Episodes(tid, season, episode),
PRIMARY KEY (uid, tid, season, episode)
);
CREATE TABLE TVRatings (
uid int,
tid int,
rating int check (rating > 0 and rating < 6),
FOREIGN KEY(uid, tid) REFERENCES WatchedEpisodes(uid,tid),
PRIMARY KEY(uid, tid)
);
Wondering if there is a way to only reference part of that composite key. These are only two of my tables so if further information is needed, I can add more.
This is actually in the spec, but it's not implemented yet. It's called MATCH PARTIAL
CREATE TABLE foo (
a int,
b int,
c int,
PRIMARY KEY (a,b,c)
);
CREATE TABLE bar (
a int,
b int,
FOREIGN KEY (a,b) REFERENCES foo (a,b) MATCH PARTIAL
);
You can read about it in the docs,
A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL constraints can be applied to the referencing column(s) to prevent these cases from arising.)
I think currently it's viewed as an anti-feature, so it's not likely to land anytime soon.
As a workaround, you can create another table that just has (a,b)
CREATE TABLE baz (
a int,
b int,
PRIMARY KEY (a,b)
);
From here you can link both tables to it...
CREATE TABLE foo (
a int,
b int,
c int,
PRIMARY KEY (a,b,c),
FOREIGN KEY (a,b) REFERENCES baz
);
CREATE TABLE bar (
a int,
b int,
FOREIGN KEY (a,b) REFERENCES baz
);