一个表是否可以引用另一个表并具有不在引用表中的额外键值?

Question

我想在两个表之间建立关系。

我有一个表 A (project)，project_id作为主键.
和一个表 B (organization)。项目可以有许多组织。

问题是表 B 在project_id中的值不在表 A 中(但将在即将到来的表中)。

有没有办法允许这种关系，即使不存在密钥？还是必须从表 B 中删除具有违规值的行？

Answer 1

FOREIGN KEY约束的全部目的是禁止表 B 中违反约束的行 - 并确保它。

也就是说，您可以使用多列 FK 约束的默认MATCH SIMPLE行为来获得一个优雅的解决方案：如果至少NULL一个包含的列，则不强制执行 FK。看：

• 完全匹配、简单匹配和部分匹配之间的区别？

您需要一个额外的列，即我的演示中valid布尔标志：

CREATE TABLE project (
project_id int NOT NULL GENERATED ALWAYS AS IDENTITY
, project text NOT NULL
, project_id_valid boolean NOT NULL DEFAULT true  -- NOT NULL!
, PRIMARY KEY(project_id, project_id_valid)  -- !
, CONSTRAINT project_id_always_valid CHECK (project_id_valid)  -- only true
);
COMMENT ON COLUMN project.project_id_valid IS 'Redundant column to allow conditional multicolumn FK reference in table organization';
INSERT INTO project(project) VALUES ('project1');
CREATE TABLE organization (
org_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, org text NOT NULL
, project_id integer NOT NULL
, project_id_valid boolean DEFAULT true  -- can be NULL!
, CONSTRAINT valid_project_fk FOREIGN KEY (project_id, project_id_valid) REFERENCES project -- !
, CONSTRAINT project_id_valid_or_null CHECK (project_id_valid)  -- only true
);
COMMENT ON COLUMN project.project_id_valid IS 'TRUE enforces the FK constraint valid_project_fk.';

db<>小提琴在这里

模型已简化。实际组织将有多个项目，您将使用多对多实现。看：

• 如何在PostgreSQL中实现多对多关系？

CONSTRAINT project_id_always_valid CHECK (valid)与柱子上的NOT NULL相结合，强制project.project_id_valid始终true，这使它成为一个噪声柱。我们需要它用于多列 PK 以允许我们的多列 FK 约束在organization中。(或者，在(project_id)上使用纯 PK，并在(project_id, project_id_valid)上添加多列UNIQUE约束。

对应的列organization.valid可以是true或NULL。如果它是true，则强制实施 FK 约束，否则不强制执行。实际上，您可以打开或关闭每行的 FK。正是你所追求的。

这些是标准的 SQL 功能。不过，一定要为来世记录诡计的目的。本着这种精神，我在专栏project_id_valid上添加了评论。