postgresql – 外键引用继承表

发布时间：2020-12-13 15:50:27 所属栏目：百科来源：网络整理

导读：我有以下表格： CREATE TABLE mail ( id serial,parent_mail_id integer,... PRIMARY KEY (id),FOREIGN KEY (parent_mail_id) REFERENCES mail(id),...);CREATE TABLE incoming ( from_contact_id integer NOT NULL REFERENCES contact(id),... PRIMARY KEY

我有以下表格：

CREATE TABLE mail (
    id serial,parent_mail_id integer,...

    PRIMARY KEY (id),FOREIGN KEY (parent_mail_id) REFERENCES mail(id),...
);

CREATE TABLE incoming (
    from_contact_id integer NOT NULL REFERENCES contact(id),...
    PRIMARY KEY (id),---> FOREIGN KEY (parent_mail_id) REFERENCES mail(id),<---
    ...
) INHERITS(mail);

CREATE TABLE outgoing (
    from_user_id integer NOT NULL REFERENCES "user"(id),...  
    PRIMARY KEY (id),--> FOREIGN KEY (parent_mail_id) REFERENCES mail(id),<--
    ...
) INHERITS(mail);

传入和传出继承自邮件并再次定义其外键(和主键),因为它们不会自动继承.

问题是：

如果我要插入传入的邮件,则无法从传出表中引用它,因为外键仅适用于超级表(邮件).

有解决方法吗？

解决方法

PostgreSQL 9.3 docs：

A serious limitation of the inheritance feature is that indexes
(including unique constraints) and foreign key constraints only apply
to single tables,not to their inheritance children. This is true on
both the referencing and referenced sides of a foreign key constraint.
Thus,in the terms of the above example:

If we declared cities.name to be UNIQUE or a PRIMARY KEY,this would not stop the capitals table from having rows with names
duplicating rows in cities. And those duplicate rows would by default
show up in queries from cities. In fact,by default capitals would
have no unique constraint at all,and so could contain multiple rows
with the same name. You could add a unique constraint to capitals,but
this would not prevent duplication compared to cities.

Similarly,if we were to specify that cities.name REFERENCES some other table,this constraint would not automatically propagate to
capitals. In this case you could work around it by manually adding the
same REFERENCES constraint to capitals.

Specifying that another table’s column REFERENCES cities(name) would allow the other table to contain city names,but not capital
names. There is no good workaround for this case.

These deficiencies will probably be fixed in some future release,but
in the meantime considerable care is needed in deciding whether
inheritance is useful for your application.

并非真正的解决方法,因此可能使邮件成为非继承表,然后将incoming_columns和outgoing_columns分别用于各自的额外列,邮件ID作为主键和外键.然后,您可以创建一个视图传出作为邮件INNER JOIN outgoing_columns,例如.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!