How to delete rows with specific column that hold duplicate values where each has constraint?

Question

DELETE FROM staff
WHERE staff_id NOT IN (SELECT MIN(staff_id) FROM staff GROUP BY store_id);

There is an error:

[23503] ERROR: update or delete on table "staff" violates foreign key constraint "payment_staff_id_fkey" on table "payment" Detail: Key (staff_id)=(3) is still referenced from table "payment".

Indeed, payment has FK constraint:

CREATE TABLE public.payment
(
  payment_id integer NOT NULL DEFAULT nextval('payment_payment_id_seq'::regclass),
  customer_id integer NOT NULL,
  staff_id smallint NOT NULL,
  rental_id integer NOT NULL,
  amount numeric(5,2) NOT NULL,
  payment_date timestamp without time zone NOT NULL,
  CONSTRAINT payment_pkey PRIMARY KEY (payment_id),
  CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id)
      REFERENCES public.customer (customer_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT payment_rental_id_fkey FOREIGN KEY (rental_id)
      REFERENCES public.rental (rental_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT payment_staff_id_fkey FOREIGN KEY (staff_id)
      REFERENCES public.staff (staff_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (
  OIDS=FALSE
);

It is not possible to set payment.staff_id = null because of NOT NULL as suggested here.

What is the easiest way to solve this?

`WHERE store_id NOT IN (SELECT MIN(staff_id) FROM staff GROUP BY store_id)` this doesn't look good, you are comparing store_ids to staff_ids. — joop, May 23 '16 at 11:16
Which means you try to delete all staff, except for one staff member per store. Highly improbable this is what you really want. — joop, May 23 '16 at 11:34
It's exactly what I need `delete all staff, except for one staff member per store` since I have @OneToOne mapping in Hibernate that fails otherwise. Maybe I should modify some constraints to `ON DELETE CASCADE DEFERRABLE` ..... — J.Olufsen, May 23 '16 at 11:36
And contrary to the title of your question, it are not duplicates, it are different persons, who happen to work for the same store. — joop, May 23 '16 at 11:50

NLink · Accepted Answer · 2016-05-23T11:50:57.880

Looks like it's a problem of a data model, not a db.

At this moment constraints strictly forbid you to execute such deletion. Looks like there is a strictly rule "Every payment does have to be linked with a staff member who worked with it and payment info should remain in db". So, if there is a need in deleteion of staff member record you have to change that rule. Here are some options:

Not every payment does have to be linked with a staff member who worked with it and payment info should remain in db. In that case you just have to make staff_id nullable and alter constraint to ON DELETE SET NULL
Every payment does have to be linked with a staff member who worked with it and payment info not necessarily should remain in db. If you want to delete payments processed by removed staff member, change ON DELETE RESTRICT to ON DELETE CASCADE
Every payment does have to be linked with a staff member who worked with it or another one and payment info should remain in db. May be it's possible to re-reference payment to another staff member, for example head of organization. In such case you should change constraint to ON DELETE SET DEFAULT (and set a default value ofc).
One more option is to keep staff member id by marking record in public.staff as archive instead of deleting. If so, you need a way to do so: some bool column in public.staff or something like that.

Anyway it's firstly issue of business logic but not technical issue.

Edit: It's either question has been altered or I missed note "When trying to delete duplicates". If so, you just need to update entities linked with stuff members to de-reference them to actual ones instead of duplicates before deletion.

How to delete rows with specific column that hold duplicate values where each has constraint?

1 Answers1