postgre如果主键有多列，如何引用SQL表

Question

我正在学习postgresql，我已经创建了两个表：goals和results。每个表都有一个主键，由3列组成：

• id
• valid_date_from
• vald_untl_from

我这样做是为了使每一行都必须是唯一的，不仅取决于其id，还取决于目标或结果何时仍然有效。这是我的sql代码：

CREATE TABLE goals (
goal_id INT,
goal_title VARCHAR(80),
goal_description VARCHAR(300),
goal_valid_from_date TIMESTAMP,
goal_valid_until_date TIMESTAMP,
goal_deleted_flag BOOLEAN,
PRIMARY KEY (goal_id, goal_valid_from_date, goal_valid_until_date)
);
CREATE TABLE results (
result_id INT,
goal_id INT,
result_description VARCHAR(300),
result_target FLOAT,
result_timestamp DATE,
result_valid_from_date TIMESTAMP,
result_valid_until_date TIMESTAMP,
result_deleted_flag BOOLEAN,
PRIMARY KEY (result_id, result_valid_from_date, result_valid_until_date)
);

我现在的目标是创建一个外键，这样我就可以只基于goal_id列而不基于valid_from/until_date列连接这两个表，否则它们永远不会匹配。

我试图通过使用以下代码行来实现这一点：

ALTER TABLE results
ADD FOREIGN KEY(goal_id)
REFERENCES goals(goal_id) on delete set null;

然而，我得到了一个错误：

SQL错误[42830]：错误：没有给定唯一的约束匹配引用表的键"；目标；

你能提出一种聪明优雅的方式来实现我的目标吗？

Answer 1

在文档中非常清楚

FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]

您需要放置column_name 的列表

Answer 2

。。。以便我能够仅基于goal_id列连接这两个表。。。

如错误消息所示，您需要添加一个匹配的UNIQUE CONSTRAINT:

ALTER TABLE goals
ADD CONSTRAINT u_goals_goal_id
UNIQUE (goal_id)

然而，这个约束比实际的主键更具限制性，如果您当前的设计是有意的，而不是偶然的，那么主键可能不是您想要的(请参阅@MikeOrganek和@ErwinBrandstetter的评论(。

如果这是你想要的，那么你应该考虑把它作为你的主键。

对于你的排除问题(没有两个目标可能有重叠的时间段(，看看手册中的例子，它从字面上描述了你的情况。