Delete parent if it's not referenced by any other child
In PostgreSQL 9.1 or later you can do this with a single statement using a data-modifying CTE. This is generally less error prone. It minimizes the time frame between the two DELETEs in which a race conditions could lead to surprising results with concurrent operations:
WITH del_child AS (
DELETE FROM child
WHERE child_id = 1
RETURNING parent_id, child_id
)
DELETE FROM parent p
USING del_child x
WHERE p.parent_id = x.parent_id
AND NOT EXISTS (
SELECT 1
FROM child c
WHERE c.parent_id = x.parent_id
AND c.child_id <> x.child_id -- !
);
db<>fiddle here
Old sqlfiddle
The child is deleted in any case. I quote the manual:
Data-modifying statements in
WITH
are executed exactly once, and
always to completion, independently of whether the primary query reads
all (or indeed any) of their output. Notice that this is different
from the rule forSELECT
inWITH
: as stated in the previous section,
execution of aSELECT
is carried only as far as the primary query
demands its output.
The parent is only deleted if it has no other children.
Note the last condition. Contrary to what one might expect, this is necessary, since:
The sub-statements in
WITH
are executed concurrently with each other
and with the main query. Therefore, when using data-modifying
statements inWITH
, the order in which the specified updates actually
happen is unpredictable. All the statements are executed with the same
snapshot (see Chapter 13), so they cannot "see" each others' effects
on the target tables.
Bold emphasis mine.
I used the column name parent_id
in place of the non-descriptive id
.
Eliminate race condition
To eliminate possible race conditions I mentioned above completely, lock the parent row first. Of course, all similar operations must follow the same procedure to make it work.
WITH lock_parent AS (
SELECT p.parent_id, c.child_id
FROM child c
JOIN parent p ON p.parent_id = c.parent_id
WHERE c.child_id = 12 -- provide child_id here once
FOR NO KEY UPDATE -- locks parent row.
)
, del_child AS (
DELETE FROM child c
USING lock_parent l
WHERE c.child_id = l.child_id
)
DELETE FROM parent p
USING lock_parent l
WHERE p.parent_id = l.parent_id
AND NOT EXISTS (
SELECT 1
FROM child c
WHERE c.parent_id = l.parent_id
AND c.child_id <> l.child_id -- !
);
This way only one transaction at a time can lock the same parent. So it cannot happen that multiple transactions delete children of the same parent, still see other children and spare the parent, while all of the children are gone afterwards. (Updates on non-key columns are still allowed with FOR NO KEY UPDATE
.)
If such cases never occur or you can live with it (hardly ever) happening - the first query is cheaper. Else, this is the secure path.
FOR NO KEY UPDATE
was introduced with Postgres 9.4. Details in the manual. In older versions use the stronger lock FOR UPDATE
instead.
Hibernate : delete parent not referenced by children
Hibernate won't do it magically for you. You'll have to implement it yourself:
Company company = customer.getCompany();
company.removeCustomer(customer);
session.delete(customer);
if (company.getCustomers().isEmpty()) {
session.delete(company);
}
Delete Parent record if child is not present
Without seeing your table structure it's difficult to tell you the exact query you would need but, if I understand your question correctly, you just need to do something like this:
DELETE T
FROM MyTable T
WHERE NOT EXISTS(SELECT * FROM MyTable WHERE ParentID = T.MenuID)
AND T.ParentID IS NULL
This query does a correlated subquery to find all the menu records that don't have child records. It uses SQL's EXISTS clause
why SQL cannot delete parent row when references are deleted
The reason why you can delete rows from child tables, but not from parent tables, is because of the referential integrity that you added to the child tables when you added foreign keys in the child tables which reference to primary keys in parent tables.
For example, in order to delete a row from table User
with key ID = 1
, you would first need to delete all rows where the foreign key user_ID = 1
in the following tables :
- Activity
- WorkflowMessage
- Document
- Group
As an alternative to manually deleting child rows, what you can do is redefine the foreign keys with ON DELETE CASCADE
, i.e.:
CREATE TABLE Activity
(
ID INT generated by default as identity PRIMARY KEY,
... other columns
user_ID INTEGER,
CONSTRAINT fk_activity_usr FOREIGN KEY (user_ID)
REFERENCES EDMSDATABASE."User"(ID) ON DELETE CASCADE -- Cascading delete
);
What will happen here is that if the referenced parent User.ID
row is deleted, all the child rows linked in table Activity
be deleted. (You'll need to repeat the above for all child tables referencing User.Id
) Use this option with care!
Another alternative is to allow the foreign key to be null in the child table, and then to specify ON DELETE SET NULL
in the foreign key:
CREATE TABLE Activity
(
ID INT generated by default as identity PRIMARY KEY,
... other columns
user_ID INTEGER NULL, -- Nullable
CONSTRAINT fk_activity_usr FOREIGN KEY (user_ID)
REFERENCES EDMSDATABASE."User"(ID) ON DELETE SET NULL
);
As with the cascading delete, if a parent User.Id
row is deleted, then all referencing child rows in Activity
will have the UserId
updated to NULL (but not deleted). Again, be cautious of this approach as this can cause child rows to become orphaned.
How do I keep data B on a child table that is connected to data A on the parent table, while deleting data A on the parent table?
One option would be to change the foreign key definition to on delete set null
instead of on delete cascade
- this also requires making the column null
able (which it currently isn't):
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
As explained in the documentation, this option causes the referencing column(s) in the referencing row(s) to be set to nulls [...], when the referenced row is deleted.
Side note: in general, I would not actually recommend deleting users from your referential table. Instead, you could have another column in table users
that indicate that the user was deleted (say, a boolean column called active
), and that you could use as a filter in your queries. This is safer in some ways, and avoids the need to play around with the foreign key later on.
Delete Child Records with Missing Parent
You can use a Common Table Expression to recurse the
-- Begin Create Test Data
SET NOCOUNT ON
CREATE TABLE #ThingTable (
ID INT NOT NULL,
[Name] VARCHAR(255) NOT NULL,
[ParentID] INT NULL
)
CREATE TABLE #ChildThingTable (
ID INT NOT NULL,
[Color] VARCHAR(255) NOT NULL,
)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1000,'Thing1',NULL)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1001,'Thing2',1000)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1002,'Thing3',1000)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1003,'Thing4',1000)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1004,'Thing5',1003)
INSERT INTO #ChildThingTable ( ID, Color ) VALUES ( 1001 , 'Blue')
INSERT INTO #ChildThingTable ( ID, Color ) VALUES ( 1002 , 'Black')
INSERT INTO #ChildThingTable ( ID, Color ) VALUES ( 1003 , 'Green')
INSERT INTO #ChildThingTable ( ID, Color ) VALUES ( 1004 , 'Red')
SET NOCOUNT OFF
GO
-- End Create Test Data
-- This is a batch, but could easily be a stored procedure.
DECLARE @InputID INT
SET @InputID = 1000;
SET NOCOUNT ON
DECLARE @Temp TABLE(ID INT NOT NULL);
WITH ThingCTE (ID, ParentID, [Level])
AS
(
SELECT tt1.ID, tt1.ParentID, 1 AS [Level]
FROM #ThingTable tt1
WHERE tt1.ID = @InputID
UNION ALL
SELECT tt2.ID, tt2.ParentID, tc1.[Level]+1
FROM #ThingTable tt2
JOIN ThingCTE tc1 ON (tt2.ParentID = tc1.ID)
)
INSERT INTO @Temp
( ID )
SELECT ID
FROM ThingCTE
SET NOCOUNT OFF
DELETE ctt
-- Output is for debug purposes, should be commented out in production.
OUTPUT Deleted.*
FROM #ChildThingTable ctt
JOIN @Temp t ON (ctt.ID = t.ID);
DELETE tt
-- Output is for debug purposes, should be commented out in production.
OUTPUT Deleted.*
FROM #ThingTable tt
JOIN @Temp t ON (tt.ID = t.ID)
DROP TABLE #ChildThingTable;
DROP TABLE #ThingTable;
Automatic delete a sub-object when it is not longer referenced by any parent in SQLAlchemy
SQLAlchemy has a delete-orphan
cascade that you can read about here. However, it does not work for many to many relationships since it requires that objects have "single parents":
delete-orphan cascade implies that each child object can only have one parent at a time, so is configured in the vast majority of cases on a one-to-many relationship. Setting it on a many-to-one or many-to-many relationship is more awkward; for this use case, SQLAlchemy requires that the relationship() be configured with the single_parent argument, establishes Python-side validation that ensures the object is associated with only one parent at a time.
This question discusses a similar situation as yours.
Delete nested rows from table with orphaned parent id
You can redefine the table:
CREATE TABLE tablename (
`id` INTEGER PRIMARY KEY,
`parentId` INTEGER,
`title` VARCHAR(6),
FOREIGN KEY (`parentId`) REFERENCES tablename(`id`) ON DELETE CASCADE
);
so that the column parentId
is a foreign key referencing the column id
which must be the PRIMARY KEY
of the table or must have a UNIQUE
index/constraint.
The ON DELETE CASCADE
action makes sure that when you delete a row all the children rows will also be deleted (also the children of the children rows and so on).
Also instead of 0
you must set the top level items to null
so there is no foreign key constraint violation.
You must turn on foreign key support because it is off by default and this can be done in SQLiteOpenHelper
's onConfigure()
method:
@Override
public void onConfigure(SQLiteDatabase db){
db.setForeignKeyConstraintsEnabled(true);
}
Now when you delete a row from the table all the rows of the levels below the level of the row that you deleted will also be deleted.
You may have to uninstall the app from the device so that the database is deleted and rerun to recreate the database and the table with the new definition.
See a demo.
Related Topics
Why Doesn't SQL Support "= Null" Instead of "Is Null"
MySQL #1140 - Mixing of Group Columns
Automate Version Number Retrieval from .Dtsx Files
Alternate of Lead Lag Function in SQL Server 2008
Query a Database Based on Result of Query from Another Database
Postgresql Generate Sequence with No Gap
Reference an Alias Elsewhere in the Select List
Calculate the Number of Records for Each Date Between 2 Dates
Autoincrement in Oracle to Already Created Table
Which Database Design Gives Better Performance
Produce Distinct Values in String_Agg
Execute Dynamic Query with Go in SQL
Inner Join in Update SQL for Db2