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 bySET 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
Executing a Stored Procedure Within a Stored Procedure
Get Top 10 Products for Every Category
How to Replace Null Values with a Text
Create Unqiue Case-Insensitive Constraint on Two Varchar Fields
Rails, Ransack: How to Search Habtm Relationship for "All" Matches Instead of "Any"
Identity_Insert Is Already on for Table 'X'. Cannot Perform Set Operation for Table 'Y'
Prepend Table Name to Each Column in a Result Set in SQL? (Postgres Specifically)
How to Return Only Work Time from Reservations in Postgresql
SQL Statements with Equals VS In
SQL Query - Sum(Case When X Then 1 Else 0) for Multiple Columns
Snowflake: "SQL Compilation Error:... Is Not a Valid Group by Expression"
Strategies for Checking Isnull on Varbinary Fields
How to Get a Wpf Datagrid to Save Changes Back to the Database
Sql: Sort by Priority, But Put 0 Last
Update Columns with Null Values
Insert Manually into a Table by SQL Statement, But Key Is Autoincremented