I maintain a Django app with a postgresql backend, where users post interesting URLs (links) and then either leave replies under these or upvote/downvote them. All this is done via 4 postgresql tables links_link, links_publicreply, links_vote and links_seen (links_seen keeps track of which publicreplies were seen by a user; the other 3 tables do what their names indicate).
I'm running maintenance queries whereby I'm deleting mature records from links_publicreply and related rows from links_seen. The queries go like so:
begin;
DELETE FROM links_seen WHERE which_reply_id in (SELECT id FROM links_publicreply where "submitted_on" < now() - interval '7 days');
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';
commit;
This fails with the error:
ERROR: update or delete on table "links_link" violates foreign key
constraint "links_vote_link_id_fkey" on table "links_vote" DETAIL:
Key (id)=(2207250) is still referenced from table "links_vote".
I've grappled with this for over 10 days, but can't fix it (beginner DBA here). Can anyone point out (i) what the problem is, (ii) what do I need to do to fix it so that my maintenance queries start working (I'm essentially just deleting publicreplies older than 7 days)? An illustrative example would be nice. Thanks in advance!
These tables are described as follows. links_link:
Table "public.links_link"
Column | Type | Modifiers
-----------------+--------------------------+---------------------------------------------------------
id | integer | not null default nextval('links_link_id_seq'::regclass)
description | text | not null
submitter_id | integer | not null
submitted_on | timestamp with time zone | not null
rank_score | double precision | not null
url | character varying(250) | not null
cagtegory | character varying(25) | not null
image_file | character varying(100) |
reply_count | integer | default 0
device | character varying(10) | default '1'::character varying
latest_reply_id | integer |
Indexes:
"links_link_pkey" PRIMARY KEY, btree (id)
"links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
"links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
"publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
(END)
links_vote:
Table "public.links_vote"
Column | Type | Modifiers
----------+---------+---------------------------------------------------------
id | integer | not null default nextval('links_vote_id_seq'::regclass)
voter_id | integer | not null
link_id | integer | not null
value | integer |
Indexes:
"links_vote_pkey" PRIMARY KEY, btree (id)
"links_vote_link_id" btree (link_id)
"links_vote_voter_id" btree (voter_id)
Foreign-key constraints:
"links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
"links_vote_voter_id_fkey" FOREIGN KEY (voter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
links_publicreply:
Table "public.links_publicreply"
Column | Type | Modifiers
-----------------+--------------------------+----------------------------------------------------------------
id | integer | not null default nextval('links_publicreply_id_seq'::regclass)
submitted_by_id | integer | not null
answer_to_id | integer | not null
submitted_on | timestamp with time zone | not null
description | text | not null
category | character varying(20) | not null
seen | boolean | not null
abuse | boolean | not null
device | character varying(10) | default '1'::character varying
Indexes:
"links_publicreply_pkey" PRIMARY KEY, btree (id)
"links_publicreply_answer_to_id" btree (answer_to_id)
"links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
"links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
"links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
links_seen:
Table "public.links_seen"
Column | Type | Modifiers
----------------+--------------------------+---------------------------------------------------------
id | integer | not null default nextval('links_seen_id_seq'::regclass)
seen_status | boolean | not null
seen_user_id | integer | not null
seen_at | timestamp with time zone | not null
which_reply_id | integer |
Indexes:
"links_seen_pkey" PRIMARY KEY, btree (id)
"links_seen_seen_user_id" btree (seen_user_id)
"links_seen_which_reply_id" btree (which_reply_id)
Foreign-key constraints:
"links_seen_seen_user_id_fkey" FOREIGN KEY (seen_user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
"links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
Best Answer
When you delete from
links_publicreply, it cascades that to delete child rows fromlinks_linkwhich would otherwise become orphaned (because the constraint is defined withON DELETE CASCADE).But some of those rows being deleted in
links_linkare still referenced by rows inlinks_vote. The constraint betweenlinks_linkandlinks_voteis not defined withON DELETE CASCADE, so you get an error. If you want to automatically delete the rows fromlinks_vote, change the constraint to beON DELETE CASCADE. If that is not what you want, then it is not clear what you do want, you will have to explain it some more.