CODEWITHSUNDEEP
sqldatabasepostgresqlforeign-key-relationship
rahul
rahul

Reputation: 2289

Foreign key constraint violation when foreign key is present

I am new to PostgreSQL and databases in general and am trying to figure out why I am getting a foreign key violation even when the foreign key is present.

The query I ran

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

This fails with

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".

The following select query also works fine and gives back 43068778:

select id  from entry_entry_master where id=43068778;

The entire create table command - which is the output of django sqlall is

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;

So how is it possible to get a foreign key constraint violation when the foreign key is present? Am I missing something obvious?

Upvotes: 3

Views: 3579

Answers (1)

rahul
rahul

Reputation: 2289

I figured this out myself. :(

One detail I missed is that entry_entry_master is partitioned using table inheritance. From the postgres documentation: 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.

This also explains why the select query works.

Upvotes: 6

Related Questions