PostgreSQL中的外键+表继承？ [英] Foreign keys + table inheritance in PostgreSQL?

问题描述

我有三张桌子： organization ， organization_teams 和 org_users 。这里 organization_teams 继承自 organization 。因此，如果在 organizations_teams 中添加了一条记录，它将获得组织表 id 作为 id 的值 organization_teams 中的列。

I have three tables: organization, organization_teams and org_users. Here organization_teams is inherited from organization. So suppose if a record is added in organizations_teams it will get the organization table id as value for id column in organization_teams.

org_users 在 id 列上有外键组织。现在，当我尝试在 org_users 中插入数据时，它给出了如下错误

org_users has foreign key on id column of organization. Now when I try to insert data in org_users it giving me error as below

insert or update on table "org_users" violates foreign key constraint "org_users_organizations"
DETAIL:  Key (org_id)=(12) is not present in table "organizations"

为什么？

推荐答案

这是发现在用户手册中。

简短版本：您可以使用外键或表继承，但不能同时使用两者。这本身并不是不可能的，只是技术上很难以快速，可靠的方式实现跨PostgreSQL中的继承表的唯一索引。没有它，你就不能拥有有用的外键。没有人能够成功地实现它，以便补丁添加支持即可被PostgreSQL接受。

The short version: you can use foreign keys, or table inheritance, but not both. This isn't inherently impossible, it's just that it's technically quite difficult to implement unique indexes that span inherited tables in PostgreSQL in a fast, reliable manner. Without that, you can't have a useful foreign key. Nobody's successfully implemented it well enough for a patch adding support to be accepted into PostgreSQL yet.

外键可以指向作为继承层次结构的一部分的表，但是它只会在该表中找到完全的行。不在任何父表或子表中。要查看外键看到的行，请执行 SELECT * FROM ONLY thetable 。 ONLY 关键字表示忽略继承，这就是外键查找将执行的操作。

A foreign key can point to a table that is part of an inheritance hierarchy, but it'll only find rows in that table exactly. Not in any parent or child tables. To see which rows the foreign key sees, do a SELECT * FROM ONLY thetable. The ONLY keyword means "ignoring inheritance" and that's what the foreign key lookup will do.

这篇关于PostgreSQL中的外键+表继承？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！