PostgreSQL继承和外键引用父表 [英] Postgresql inheritance and foreign key referencing parent table
问题描述
我已经对此进行了文档记录,并阅读了其他用户对此的文章,但是在我的情况下,引用应该可以正常工作:我有几个表扩展了一个实体表和一个关联表,仅引用了实体表。因此,我仅引用拥有其他所有表ID的父表。
I already documented about this and read other users post about this on so, but in my case the referencing should be working fine: I have several tables extending one "entity" table and an "association" table referencing just "entity" table. So I'm referencing just the parent table which own every other table id. How come I get the following then?
ERROR: insert or update on table "association" violates foreign key constraint "association_id1_fkey"
DETAIL: Key (id1)=(1) is not present in table "entity".
这里是我正在使用的架构。
Here is the schema I'm using.
CREATE TABLE entity (
id serial primary key,
created_at int,
updated_at int,
deleted_at int
);
CREATE TABLE association (
id1 int references entity(id) on delete cascade on update cascade,
atype varchar,
id2 int references entity(id) on delete cascade on update cascade,
created_at int,
deleted_at int
);
CREATE TABLE "user" (
first_name varchar(255),
last_name varchar(255)
)INHERITS(entity);
CREATE TABLE "pet" (
name varchar(255)
)INHERITS(entity);
INSERT INTO "user" (first_name) VALUES ('damiano');
INSERT INTO "user" (first_name) VALUES ('francesco');
INSERT INTO "user" (first_name) VALUES ('romolo');
INSERT INTO "pet" (name) VALUES ('baloo');
INSERT INTO "pet" (name) VALUES ('micia');
INSERT INTO "pet" (name) VALUES ('ioria');
INSERT INTO "association" VALUES (1, 'pets', 4, 0, 0);
INSERT INTO "association" VALUES (1, 'pets', 5, 0, 0);
INSERT INTO "association" VALUES (2, 'pets', 4, 0, 0);
INSERT INTO "association" VALUES (2, 'pets', 5, 0, 0);
INSERT INTO "association" VALUES (3, 'pets', 6, 0, 0);
正确插入行:
testing=# select * from "entity";
id | created_at | updated_at | deleted_at
----+------------+------------+------------
1 | | |
2 | | |
3 | | |
4 | | |
5 | | |
6 | | |
(6 rows)
testing=# select * from "user";
id | created_at | updated_at | deleted_at | first_name | last_name
----+------------+------------+------------+------------+-----------
1 | | | | damiano |
2 | | | | francesco |
3 | | | | romolo |
(3 rows)
testing=# select * from "pet";
id | created_at | updated_at | deleted_at | name
----+------------+------------+------------+-------
4 | | | | baloo
5 | | | | micia
6 | | | | ioria
(3 rows)
testing=#
推荐答案
父表不包含继承表中的所有数据。从该表中进行选择实际上会对继承的表执行 UNION
。
The parent table doesn't contain all the data from the inherited tables. Selecting from that table in effect does a UNION
over the inherited tables.
比较以下内容:
SELECT * FROM "entity";
SELECT * FROM ONLY "entity";
这就是为什么不再使用继承的原因。
That's why inheritance isn't used more.
这篇关于PostgreSQL继承和外键引用父表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!