PostgreSQL: table referencing only part of a composite primary key

Question

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.

Evan Carroll · Answer 1 · 2017-04-23T01:12:11.303

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
);

Do you know of another way I can go about this problem if I can't do a partial match? — Hannah Riedman, Apr 23 '17 at 01:08
@HannahRiedman updated. If this answer was useful to you than please consider marking it as chosen and flagging the question to moved to [dba.se] where it should probably be. ;) — Evan Carroll, Apr 23 '17 at 01:12

PostgreSQL: table referencing only part of a composite primary key

1 Answers1