PostgreSQL条件外键

Question

在PostgreSQL中是否可以有条件地添加外键？

类似这样的语法：ALTER TABLE table1 ADD FOREIGN KEY (some_id) REFERENCES other_table WHERE some_id NOT IN (0,-1) AND some_id IS NOT NULL;

具体而言，我的参考表只包含正整数（1+），但我需要添加外键到的表中可以包含零（0）、空值和负一（-1），它们都表示不同的意思。

注意:

我完全意识到这是糟糕的表设计，但这是10多年前建立的一个聪明技巧，当时我们可用的功能和资源还不存在。这个系统正在运行数百家零售店，所以回去改变方法可能需要数月时间，而我们没有这么多时间。

我不能使用触发器，必须使用外键完成。

- trex005

Answer 1

简短的回答是不，Postgres没有条件外键。以下是一些您可能考虑的选项：

1. 不要使用FK约束。将此逻辑移入数据访问层，并放弃引用完整性。
2. 允许在列中使用NULL，即使使用FK约束也是完全有效的。然后，使用另一个列来存储0和-1的含义。
3. 在被引用表中添加0和-1的虚拟行。即使它只有虚假数据，它也能满足FK约束。

希望这可以帮到您！

- Mike Christensen

---

这些基本上是我所预期的选项。我会把这个问题留下一段时间，希望我们是错误的。 :) - trex005

第四个选项总是修改Postgres代码，它是开源的，你知道的 :) - Mike Christensen

我之前曾经在PG源代码上搞了一番，以解决一些损坏数据的问题，那已经足够了。 - Mike Christensen

Answer 2

您可以在table1中添加另一列"shadow"，其中保存清理后的值（即除了0和-1之外的所有内容）。使用此列进行引用完整性检查。这个影子列通过对table1的简单触发器更新/填充，该触发器将0和-1之外的所有值写入影子列。0和-1都可以映射为null。

然后，您就有了引用完整性和未更改的原始列。缺点是：您还需要一个小触发器和一些冗余数据。但遗憾的是，这是遗留架构的命运！

- A.H.

---

太聪明了！你说的没错，虽然有点丑陋，但数据库仍然可以自我管理，所以应该能行！ - trex005

啊，你有没有接触过Postgres源代码？通过在幕后执行这个操作，你可以添加一个很酷的功能。 - trex005

@trex005：我没有在PG源代码上工作过。但是我经常在黑客列表上潜水，我的印象是这样的功能不会受到欢迎。如果你将_你的_示例泛化（匹配除了0和-1之外的任何内容），并考虑一个通用语法来覆盖所有可能的情况，你将得到一个相当复杂的语法结构。另一方面：使用现有工具，您可以做任何事情。净结果：稀缺资源（开发人员时间/预算）将更好地用于其他待办事项。但这只是我的印象。请随时与他们联系。 - A.H.

@A.H. 但是，像所有开源项目一样，PostgreSQL并不是由TODO列表驱动的，而是由那些积极解决问题的人们推动。虽然我怀疑PostgreSQL团队是否愿意集成解决这个特定问题的修复程序，但我也确信，如果能够创建某种合理的SQL语法来支持该结构，他们将考虑给PostgreSQL添加条件外键的工作补丁。当然，最好与团队合作，共同定义这种语法可能是什么。这是开源的，有效的代码才是王道。 - Wexxor

Answer 3

您的需求等同于此检查约束：

create table t (a float check (a >= -1 and a = floor(a) or a is null));

- Clodoaldo Neto

---

正确，但是检查约束无法检查另一个表中行的存在。 - Mike Christensen

@MikeChristensen 这个问题说引用的表格“包含所有正整数（1+）”。我知道这是不可能的，但我猜他的意思是它包含了所有没有间隔的整数直到某个限制。这可以通过添加另一个条件到检查中来解决（a <= n）。 - Clodoaldo Neto

可能吧，我只是假设原帖作者实际上想要一个真正的外键。 - Mike Christensen

Answer 4

您可以通过使用检查约束和外键来实现此功能。

CREATE TABLE table1 (some_id INT, some_id_fkey INT REFERENCES other_table(other_id), CHECK (some_id IN (0,-1) OR some_id IS NOT DISTINCT FROM some_id_fkey));

(未测试)

- Jeremy Salwen

Answer 5

这里有另一种可能性。使用PG Inheritance来强制将表分成具有+1标志列和其他情况的分区。（通常用于维护此规则/触发器。）然后，只在Has_PLUS_ONE子表和引用表之间建立FK关系。

- Andrew Lazarus

---

使用PG继承几乎不是任何问题的正确解决方案。它们太过受限和古怪。 - cliffordheath