PostgreSQL：如何限制外键的存在？
在 PostgreSQL 中，除了强制外键存在以外，最简单的方法是什么？
例如，给定下表：
create table "bar"
(
bar_id serial primary key,
status boolean not null
)
create table "foo"
(
foo_id serial primary key,
bar_id integer references "bar"
)
如何将 foo.bar_id 限制为仅 bar 中 status 为 true 的行？
我可以想象如何使用触发函数来做到这一点，但似乎我需要几个 (insert, update on foo; update，delete on bar）所以我想知道是否有更方便的方法，也许纯粹使用约束。
In PostgreSQL, what's the simplest way to enforce more than just existence on a foreign key?
For example, given the following tables:
create table "bar"
(
bar_id serial primary key,
status boolean not null
)
create table "foo"
(
foo_id serial primary key,
bar_id integer references "bar"
)
How could foo.bar_id be constrained to only rows of bar where status is true?
I can imagine how to do it with trigger functions, but it seems like I'd need several (insert, update on foo; update, delete on bar) so I'd like to know if there's a more convenient method, perhaps purely using constraints.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以将 bar.status 作为 bar 表主键的一部分。然后将状态放在 foo 表上，作为 bar 的 FK 的一部分。还设置一个约束，即 foo.status 必须等于 true。这是一种黑客行为，但根据现实世界的情况，它可能是有意义的。
You could make bar.status part of the primary key of the bar table. Then you put status on the foo table as part of the FK to bar. Also put a constraint that foo.status must equal true. It's kind of a hack, but depending on the real world context, it might make sense.
最核心的方法是在 foo 和 bar 之间放置一个中间/子类别表，其中包含状态为 true 的 bar 值。 foo 上的外键将指向中间表，但如果添加了 bar 值或状态更改为 false，则需要双方进行维护...
替代方法是使用触发器，因为外键没有过滤手段。
PostgreSQL 没有物化视图；我不确定它是否允许您指定对视图的外键引用，但这意味着如果栏状态更改为 false，则会出现问题......
The hardcore way would be to have a intermediate/subcategory table sitting between foo and bar, containing the bar values whose status is true. The foreign key on foo would point to the intermediate table, but it would require maintainance on both sides in the event that bar values are added or status changes to false...
The alternative is to use a trigger, because a foreign key has no means of filtering.
PostgreSQL doesn't have materialized views; I'm not sure if it would allow you to specify a foreign key reference to a view, but it would mean an issue in the event that a bar status changes to false...
这是标准 SQL 关系完整性无法满足现实世界要求的众多情况之一。例如，我希望可以针对索引而不是整个列创建外键； postgresql 提供部分索引，这可以解决你的问题。
如果做不到这一点，您需要将 bar 分为两个表 bar_true 和 bar_false，每个表都没有状态字段。根据 bar_true 建立您的 FK。然后，您可以创建一个视图，将 bar_true 和 bar_false 联合起来以获得完整的栏集（尽管您必须注意主键）。
This is one of the many situations in which standard SQL relational integrity falls short of meeting real world requirements. I wish, for instance, that one could create a foreign key against an index rather than an entire column; postgresql offers partial indexes, and that would solve your problem.
Failing that, you would need to divide bar into two tables bar_true and bar_false, each without the status field. Establish your FK against bar_true. You could then create a view UNIONing bar_true and bar_false together to get the complete bar set (although you'd have to take care with the primary keys).
似乎如果您使用触发器，您可以控制只让 bar 状态为 true 的记录进入 foo，并且您还可以在 bar 上放置一个触发器，如果状态变为 false，它将删除 foo 中的记录。
Seems if you used triggers you could control only letting records into foo that have a bar status of true and you could also put a trigger on bar that if a status went to false it would remove the record in foo.