Delete Rows with Foreign Key in Postgresql

Delete rows with foreign key in PostgreSQL

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.

PostgreSQL: deleting rows referenced from another table

Yes use CTEs (Common Table Expression)

WITH tmp AS (SELECT object_data_id FROM object WHERE ...),
    upd AS (UPDATE object SET object_data_id = NULL WHERE ...)
DELETE FROM object_data 
  WHERE id IN (SELECT object_data_id FROM tmp);

The first CTE called tmp is executed first and remembers the data you need later
The second CTE called upd does sets the fields to NULL
Finally the DELETE uses the data from tmp to perform the DELETE

PostgreSQL: Delete a row and all of it's references (FK) existing in others tables?

Yes, use on delete cascade on the foreign keys.

create table users (
  id bigserial primary key
  ...
);

create table posts (
  ...
  user_id bigint not null references users on delete cascade
  ...
)

Now when a user is deleted, all their associated posts will also be deleted.

This will go on, for example, if a post has comments...

create table comments (
  ...
  post_id biging not null references posts on delete cascade
  ...
)

When a user is deleted their posts will be deleted, and those posts' comments will be deleted. That's the "cascade" part.

Delete rows and other rows with a reference to the deleted row

The best solution is to create a proper foreign key that is defined with on delete cascade. Which requires to store a NULL value rather than a magic "zero" in the parent_id column:

create table family 
(
  id int primary key, 
  name varchar(5), 
  parent_id int, 
  foreign key (parent_id) references family on delete cascade
);

Then all you need is:

delete from family
where name = 'peter';

Online example

If you want to convert your existing table and data, you can do it like this:

--- change all zeroes to NULL
update family 
  set parent_id = null
where parent_id = 0;

-- add a primary key in order to be able to create a foreign key
alter table family add primary key (id);

-- add the foreign key 
alter table family 
  add foreign key (parent_id) references family (id)
  on delete cascade;

Postgres: DELETE hangs on a table with a self-referential foreign key

According to this answer, foreign keys in Postgres are implemented as triggers, so you can temporarily disable foreign key checks like this:

ALTER TABLE items DISABLE TRIGGER ALL; 
DELETE FROM items; 
ALTER TABLE items ENABLE TRIGGER ALL;

Deleting with self-referential foreign key in Postgres

No, you don't have to worry about the order in the select.

Foreign keys (unlike unique constraints) are evaluated per statement, not per row. And a common table expression is still a single statement, even if you have multiple SELECTs and DELETEs in it.

So if all constraints are still valid when the statement finishes, everything is OK.

You can easily see this with the following simple test:

CREATE TABLE fk_test
(
  id          integer PRIMARY KEY,
  parent_id   integer,
  FOREIGN KEY (parent_id) REFERENCES fk_test (id)
);

INSERT INTO fk_test (id, parent_id) 
VALUES 
  (1, null),
  (2, 1),
  (3, 2),
  (4, 1);

So the following obviously works, even if the IDs are specified in the "wrong" order:

DELETE FROM fk_test
WHERE id IN (1,2,3,4);

The following also works - showing that a CTE is still a single statement:

with c1 as (
  delete from fk_test where id = 1
), c2 as (
  delete from fk_test where id = 2
), c3 as (
  delete from fk_test where id = 3
)
delete from fk_test where id = 4;