Deleting a Row with a Self-Referencing Foreign Key

Deleting a row with a self-referencing foreign key

You can temporarily disable foreign key constraints with this query:

SET foreign_key_checks = 0;

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;

How can I Delete row from table with self reference foreign key

Drop the constraint, delete the rows you would like to delete and at the end re-create the constraint. However if you delete a row which was referenced by another rows, you should delete the child rows as well, otherwise you won't be allowed to recreate the constraint at the end of the process.

Self referencing foreign-key constraints and delete

Unlike Andomar, I'd be happy using a trigger - but I wouldn't remove the constraint checking. If you implement it as an instead of trigger, you can reset the other rows to null before performing the actual delete:

CREATE TRIGGER T_tabData_D
on tabData
instead of delete
as
    set nocount on
    update tabData set fiData = null where fiData in (select idData from deleted)
    delete from tabData where idData in (select idData from deleted)

It's short, it's succinct, it wouldn't be necessary if SQL Server could handle foreign key cascades to the same table (in other RDBMS', you may be able to just specify ON DELETE SET NULL for the foreign key constraint, YMMV).

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;

How to Delete all data from a table which contain self referencing foreign key

I don't know if I am missing something, but maybe you can try this.

UPDATE employee SET SeniorID = NULL
DELETE FROM employee

Delete entity with self-referencing foreign key using recursive function in Entity Framework

I found the error. It was my fault with recursive. I used while loop in the recursive function. So it become infinite loop. Actualy I needed to use if statement instead. I just replaced with this function. It worked fine.

private void RelatedCategories(IEnumerable<Category> categories)
        {
            foreach (var c in categories)
            {
                deletedIds.Add(c.Id);
                if(c.Categories!=null && c.Categories.Any())
                {
                    SetRelatedCategories(c.Categories);
                }
            }
        }

Ordered DELETE of records in self-referencing table

A single DELETE with a WHERE clause matching a set of records will delete those records in an implementation-defined order. This order may change based on query planner decisions, statistics, etc. No ordering guarantees are made. Just like SELECT without ORDER BY. The DELETE executes in its own transaction if not wrapped in an explicit transaction, so it'll succeed or fail as a unit.

To force order of deletion in PostgreSQL you must do one DELETE per record. You can wrap them in an explicit transaction to reduce the overhead of doing this and to make sure they all happen or none happen.

PostgreSQL can check foreign keys at three different points:

• The default, NOT DEFERRABLE: checks for each row as the row is inserted/updated/deleted
• DEFERRABLE INITIALLY IMMEDIATE: Same, but affected by SET CONSTRAINTS DEFERRED to instead check at end of transaction / SET CONSTRAINTS IMMEDIATE
• DEFERRABLE INITIALLY DEFERRED: checks all rows at the end of the transaction

In your case, I'd define your FOREIGN KEY constraint as DEFERRABLE INITIALLY IMMEDIATE, and do a SET CONSTRAINTS DEFERRED before deleting.

(Actually if I vaguely recall correctly, despite the name IMMEDIATE, DEFERRABLE INITIALLY IMMEDIATE actually runs the check at the end of the statement instead of the default of after each row change. So if you delete the whole set in a single DELETE the checks will then succeed. I'll need to double check).

(The mildly insane meaning of DEFERRABLE is IIRC defined by the SQL standard, along with gems like a TIMESTAMP WITH TIME ZONE that doesn't have a time zone).

---

Related Topics