0

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?

  • Where are you getting that syntax from? I don't see it described in the manual: postgresql.org/docs/9.4/static/sql-delete.html – Colin 't Hart May 25 '15 at 9:36
  • I got it from there, the [ USING using_list ] is the list of tables I am using. But it might not be a correct interpretation. – Erwin Rooijakkers May 25 '15 at 9:39
  • Sorry, got it! Yet the semantics are what you're missing: It only deletes from "Users", not from the tables in the "using_list" -- they're purely listed to be able to join tables to determine which rows from "Users" need to be deleted. – Colin 't Hart May 25 '15 at 9:45
  • What you are asking is typically not possible at all. A "Calendar" is typically referenced by other "Actions" of other "Users" as well, and FK constraints guarantee referential integrity, so you cannot DELETE all of it like you SELECT it. – Erwin Brandstetter May 25 '15 at 23:05
1

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!

  • Thanks. Okay, I think I will create ON DELETE CASCADE constraints anyway. – Erwin Rooijakkers May 25 '15 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.