Delete Parent If It's Not Referenced by Any Other Child

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 for SELECT in WITH: as stated in the previous section,
execution of a SELECT 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 in WITH, 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 nullable (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



Leave a reply



Submit