sql - 存在外键时违反外键约束

Question

我是 PostgreSQL 和一般数据库的新手，我想弄清楚为什么即使存在外键也会出现外键违规。

我运行的查询

insert into analytics_url_redirect
(source, news_item_id, access_date)
select 'n',id,CURRENT_TIMESTAMP from entry_entry_master
where id=43068778;

这失败了

ERROR: insert or update on table "analytics_url_redirect" violates foreign key constraint "news_item_id_refs_id_15ddd78c"
SQL state: 23503
Detail: Key (news_item_id)=(43068778) is not present in table "entry_entry_master".

以下选择查询也可以正常工作并返回 43068778:

select id  from entry_entry_master where id=43068778;

整个创建表命令——django sqlall 的输出是

BEGIN;
CREATE TABLE "analytics_url_redirect" (
    "id" serial NOT NULL PRIMARY KEY,
    "newsletter_id" integer REFERENCES "nlc_newsletter" ("newslettercore_ptr_id") DEFERRABLE INITIALLY DEFERRED,
    "alert_id" integer REFERENCES "alerts_emailtracker" ("id") DEFERRABLE INITIALLY DEFERRED,
    "source" varchar(1) NOT NULL,
    "brief_id" integer REFERENCES "brief_brief" ("id") DEFERRABLE INITIALLY DEFERRED,
    "news_item_id" integer REFERENCES "entry_entry_master" ("id") DEFERRABLE INITIALLY DEFERRED,
    "external_news_item_id" integer REFERENCES "nlc_newsletterblock" ("id") DEFERRABLE INITIALLY DEFERRED,
    "recipient_id" integer REFERENCES "subscriber_subscriber" ("id") DEFERRABLE INITIALLY DEFERRED,
    "external_recipient_id" integer REFERENCES "subscriber_pseudosubscriber" ("id") DEFERRABLE INITIALLY DEFERRED,
    "access_date" timestamp with time zone NOT NULL
)
;
CREATE INDEX "analytics_url_redirect_newsletter_id" ON "analytics_url_redirect" ("newsletter_id");
CREATE INDEX "analytics_url_redirect_alert_id" ON "analytics_url_redirect" ("alert_id");
CREATE INDEX "analytics_url_redirect_brief_id" ON "analytics_url_redirect" ("brief_id");
CREATE INDEX "analytics_url_redirect_news_item_id" ON "analytics_url_redirect" ("news_item_id");
CREATE INDEX "analytics_url_redirect_external_news_item_id" ON "analytics_url_redirect" ("external_news_item_id");
CREATE INDEX "analytics_url_redirect_recipient_id" ON "analytics_url_redirect" ("recipient_id");
CREATE INDEX "analytics_url_redirect_external_recipient_id" ON "analytics_url_redirect" ("external_recipient_id");
CREATE INDEX "analytics_url_redirect_access_date" ON "analytics_url_redirect" ("access_date");
COMMIT;

那么当存在外键时，怎么可能违反外键约束呢？我是否遗漏了一些明显的东西？

Answer 1

这是我自己想出来的。 :(

我错过的一个细节是 entry_entry_master 是使用表继承进行分区的。来自 postgres 文档:postgres Inheritance

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.

这也解释了为什么选择查询有效。