Delete referenced rows in the same PostgreSQL table-CodePudding

Example Table:

       GroupId, User, RefrenceToGroupId
Row 1: 102,     ABC,  null
Row 2: 103,     DEF,  102
Row 3: 104,     GHI,  102
Row 4: 105,     JKL,  103

What I'm trying to figure out is how to delete the parent row, which would cascade and delete all the referenced members.

For example:

Let's say I delete Row 1:

Row 1: 102, ABC, null

What should happen since Row 2 and Row 3 have a reference to Row 1 they should get deleted, and Row 4 should also be deleted as it has a reference to row 2 which is being deleted.

Any ideas on how to approach this?

CodePudding user response：

You can use on delete cascade within the same table.

  foreign key(RefrenceToGroupId)
    references groups(GroupId)
    on delete cascade

Demonstration.