I would like to delete rows which contain a foreign key, but when I try something like this:
DELETE FROM osoby WHERE id_osoby='1'
I get this statement:
ERROR: update or delete on table "osoby" violates foreign key constraint "kontakty_ibfk_1" on table "kontakty" DETAIL: Key (id_osoby)=(1) is still referenced from table "kontakty".
How can I delete these rows?
Here, ON DELETE CASCADE is added because when any row is deleted in one table the same gets deleted in the foreign referenced tables that are referencing the primary key in that table.
To drop a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student ) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint. In our example, the name of this constraint is fk_student_city_id .
First, specify the table from which you want to delete data in the DELETE FROM clause. Second, specify which rows to delete by using the condition in the WHERE clause. The WHERE clause is optional. However, if you omit it, the DELETE statement will delete all rows in the table.
To automate this, you could define the foreign key constraint with ON DELETE CASCADE
.
I quote the the manual for foreign key constraints:
CASCADE
specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.
Look up the current FK definition like this:
SELECT pg_get_constraintdef(oid) AS constraint_def FROM pg_constraint WHERE conrelid = 'public.kontakty'::regclass -- assuming public schema AND conname = 'kontakty_ibfk_1';
Then add or modify the ON DELETE ...
part to ON DELETE CASCADE
(preserving everything else as is) in a statement like:
ALTER TABLE kontakty DROP CONSTRAINT kontakty_ibfk_1 , ADD CONSTRAINT kontakty_ibfk_1 FOREIGN KEY (id_osoby) REFERENCES osoby (id_osoby) ON DELETE CASCADE;
There is no ALTER CONSTRAINT
command. Drop and recreate the constraint in a single ALTER TABLE
statement to avoid possible race conditions with concurrent write access.
You need the privileges to do so, obviously. The operation takes an ACCESS EXCLUSIVE
lock on table kontakty
and a SHARE ROW EXCLUSIVE
lock on table osoby
.
If you can't ALTER
the table, then deleting by hand (once) or by trigger BEFORE DELETE
(every time) are the remaining options.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With