PostgreSQL – Remove Data from Multiple Tables with Different Foreign Keys Without ON DELETE CASCADE

deletejoin;postgresql

I have the following SELECT statement:

SELECT * FROM "Users" u 
INNER JOIN "Calendars" c ON (u."UserId" = c."UserId")
INNER JOIN "Actions" a ON (c."CalendarId" = a."Calendar_CalendarId")
WHERE u."AuthenticationStatus" = 0 AND u."CreationDate" < now()::date - 7;

This properly selects all rows I want from the three tables "Users", "Calendars" and "Actions". It does this by matching the "UserId" of the "Users" table with the foreign key "UserId" of the "Calendars" table, and the "CalendarId" of the "Calendars" table with the foreign key "Calendar_CalendarId" of the "Actions" table.

I want to convert this to a DELETE statement. What I tried is:

DElETE FROM "Users" u 
    USING "Actions" a, "Calendars" c
WHERE u."UserId" = c."UserId" AND 
      c."CalendarId" = a."Calendar_CalendarId" AND 
      u."AuthenticationStatus" = 0 AND 
      u."CreationDate" < now()::date - 7;

But this says:

update or delete on table "Calendars" violates foreign key constraint "FK_public.Actions_public.Calendars_Calendar_CalendarId" on table "Actions"
DETAIL: Key (CalendarId)=(2) is still referenced from table "Actions".

Does anyone know how I convert this query into a DELETE statement?

Best Answer

You're interpreting the semantics of the delete statement incorrectly. When a using clause is used, it doesn't mean that records will also be deleted from those tables. Instead, those tables are purely used to join to in order to determine which rows need to be deleted from Users.

You basically have three choices:

  1. deleting child rows in a before delete on Users trigger.
  2. on delete cascade constraints.
  3. execute multiple delete statements on the various tables involved, in the right order.

My preference, in certain cases, is actually for the on delete cascade constraints, but I don't use them everywhere: just for the situation where it makes sense to be able to remove all of the children of a given parent in one go. I might use it for "invoices" and "invoice_lines".

When you take this approach you need to be sure that only users who really need to be able to delete from the parent table have that privilege -- no users or applications logging in as table owners!

Related Question