带有继承的PostgreSQL外键约束违规 [英] PostgreSQL foreign key constraint violation with inheritance

问题描述

我有一个PostgreSQL数据库，3个表和以下模式

I have a PostgreSQL database, 3 tables and my schema as follows

--- Parent Table

CREATE TABLE IF NOT EXISTS abc.parent(
    record_id         SERIAL PRIMARY KEY,
    description       text NOT NULL
);

--- Child Table


CREATE TABLE IF NOT EXISTS abc.child ( 
    total_count INT NOT NULL)  INHERITS (abc.parent);

-- Detail

CREATE TABLE abc.detail(
    detail_id      int NOT NULL,
    detail_description       text NOT NULL
    record_id         int NOT NULL,
    FOREIGN KEY (record_id) REFERENCES abc.parent(record_id)
);

然后我将记录插入到父表和子表中。

Then i insert records in to both Parent and Child tables .

父母

|record_id|description|
|1        |abcd       |
|2        |efgh       |

孩子

|record_id|description|total_count|
|3        |xygh       |5          |
|4        |mnop       |7          |

当我尝试将记录插入到明细表中之后，两次都成功了

When I try to insert record into detail table following two entires was success

Detail
|detail_id|detail_description|record_id|
|100      |detail_desc1      |  1      |
|200      |detail_desc2      |  2      |

但是我无法插入带有record_id 3的条目，这给了我一个外键违反错误

but I cant insert entry with record_id 3 it gave me a foreign key violation error

有人可以解释这个错误吗？

Can someone explain this error ??

我们可以在带有继承的Postgresql中创建像这样的外键关系吗？

推荐答案

不，那行不通。

就像文档说：

继承特性的一个严重限制是索引
（包括唯一约束）和外键约束仅将
应用于单表，不给他们继承孩子。在外键约束的引用端和被引用端上都是真实的
。

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.

全局索引是表继承的重要缺失功能之一。

“Global indexes” are one of the important missing features of table inheritance.

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