sql - PostgreSQL:表仅引用复合主键的一部分

Question

我正在使用 Postgres 9.6.2 创建电影和电视节目流媒体服务数据库(用于学校项目)。我遇到了以下错误:

没有唯一约束匹配引用表“watchedepisodes”的给定键

下面的 TVratings 表将获取一个电视节目，只要用户至少看过一集(显示在 WatchedEpisodes 表中)并允许用户对其进行评分。由于 WatchedEpisodes 具有用户 ID、电视节目 ID、季节和剧集的复合主键，它不会让我仅从该表中引用 uid 和 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)
);

想知道是否有办法只引用该组合键的一部分。这些只是我的两个表格，所以如果需要更多信息，我可以添加更多。

Answer 1

这实际上在规范中，但尚未实现。它被称为 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
);

您可以 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.)

我认为目前它被视为反功能，因此不太可能很快落地。

作为解决方法，您可以创建另一个只有 (a,b)

的表

CREATE TABLE baz (
  a int,
  b int,
  PRIMARY KEY (a,b)
);

从这里您可以将两个表链接到它...

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