Thread: DELETE trigger, direct or indirect?
Hi. This is a bit unusual. We have a foreign key between two tables, with ON DELETE CASCADE, to preserve referential integrity. But we apparently also need to preserve the severed reference (by natural key, i.e. its name), to later on reconnect the two entities after-the-fact, should the parent row re-appear later on (in the same transaction or not it still unclear).
To achieve this weird requirement, I'd like to know if it is possible in an ON DELETE trigger to know whether the deletion is coming from a direct-DELETE in the "child table", or whether the deletion is coming from the "parent table" CASCADEd to the child table.
Thanks, --DD
> On 16/02/2023 14:23 CET Dominique Devienne <ddevienne@gmail.com> wrote: > > Hi. This is a bit unusual. We have a foreign key between two tables, with > ON DELETE CASCADE, to preserve referential integrity. But we apparently > also need to preserve the severed reference (by natural key, i.e. its name), > to later on reconnect the two entities after-the-fact, should the parent > row re-appear later on (in the same transaction or not it still unclear). > > To achieve this weird requirement, I'd like to know if it is possible in an > ON DELETE trigger to know whether the deletion is coming from a direct-DELETE > in the "child table", or whether the deletion is coming from the "parent > table" CASCADEd to the child table. Not to my knowledge. ON DELETE CASCADE behaves like a manual DELETE on the child table that happens before the DELETE on the parent table. The process you describe shows that it's not known until the end of the transaction which parent rows can be deleted. You can instead track the parent rows as candidates for deletion in a temp table. Insert the primary key of parent rows if you deem them deletable and delete the primary key if you detect the opposite. At the end the temp table only contains IDs of parent rows that can be deleted for sure. -- Erik
On 2/16/23 05:23, Dominique Devienne wrote: > Hi. This is a bit unusual. We have a foreign key between two tables, > with ON DELETE CASCADE, to preserve referential integrity. But we > apparently also need to preserve the severed reference (by natural key, > i.e. its name), to later on reconnect the two entities after-the-fact, > should the parent row re-appear later on (in the same transaction or not > it still unclear). This is going to need a more detailed description of the relationship between the two tables: 1) The actual FK relationship. 2) What "...preserve the severed reference (by natural key, i.e. its name)" means? 3) What information will be used to reconnect the child rows to the parent rows? > > To achieve this weird requirement, I'd like to know if it is possible in > an ON DELETE trigger to know whether the deletion is coming from a > direct-DELETE in the "child table", or whether the deletion is coming > from the "parent table" CASCADEd to the child table. > > Thanks, --DD -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 2/16/23 05:23, Dominique Devienne wrote:
> Hi. This is a bit unusual. We have a foreign key between two tables,
> with ON DELETE CASCADE, to preserve referential integrity. But we
> apparently also need to preserve the severed reference (by natural key,
> i.e. its name), to later on reconnect the two entities after-the-fact,
> should the parent row re-appear later on (in the same transaction or not
> it still unclear).
This is going to need a more detailed description of the relationship
between the two tables:
1) The actual FK relationship.
2) What "...preserve the severed reference (by natural key, i.e. its
name)" means?
3) What information will be used to reconnect the child rows to the
parent rows?
Maybe the OP should be using ON DELETE SET NULL instead of CASCADE?
David J.
On 2/16/23 08:55, David G. Johnston wrote: > On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 2/16/23 05:23, Dominique Devienne wrote: > > Hi. This is a bit unusual. We have a foreign key between two tables, > > with ON DELETE CASCADE, to preserve referential integrity. But we > > apparently also need to preserve the severed reference (by > natural key, > > i.e. its name), to later on reconnect the two entities > after-the-fact, > > should the parent row re-appear later on (in the same transaction > or not > > it still unclear). > > This is going to need a more detailed description of the relationship > between the two tables: > > 1) The actual FK relationship. > > 2) What "...preserve the severed reference (by natural key, i.e. its > name)" means? > > 3) What information will be used to reconnect the child rows to the > parent rows? > > > Maybe the OP should be using ON DELETE SET NULL instead of CASCADE? That is where I am headed, however it will need more information to determine whether that makes sense or not. > > David J. -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Feb 16, 2023 at 5:59 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 2/16/23 08:55, David G. Johnston wrote:
> On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 2/16/23 05:23, Dominique Devienne wrote:
> > Hi. This is a bit unusual. We have a foreign key between two tables,
> > with ON DELETE CASCADE, to preserve referential integrity. But we
> > apparently also need to preserve the severed reference (by
> natural key,
> > i.e. its name), to later on reconnect the two entities
> after-the-fact,
> > should the parent row re-appear later on (in the same transaction
> or not
> > it still unclear).
>
> This is going to need a more detailed description of the relationship
> between the two tables:
>
> 1) The actual FK relationship.
>
> 2) What "...preserve the severed reference (by natural key, i.e. its
> name)" means?
>
> 3) What information will be used to reconnect the child rows to the
> parent rows?
>
>
> Maybe the OP should be using ON DELETE SET NULL instead of CASCADE?
That is where I am headed, however it will need more information to
determine whether that makes sense or not.
OK, I started writing SET NULL won't help, but I'll back up and try to give more info, as requested.
Pseudo SQL at this point.
create table entity (name text primary key, ...);
create table enity_list (name text primary key, ...);
create table entity_list_member(
list_name text not null references entity_list(name) on delete cascade on update cascade,
entity_name text not null references entity(name) on delete cascade on update cascade
primary key (list_name, entity_name)
);
Above is the current situation. When the entity is deleted, it's implicitly deleted from all list that mention it.
Referential Integrity 101 I guess. But apparently, it's common enough for an entity to be deleted and reloaded,
not necessarily in the same transaction, that losing the list(s) membership on delete is considered "a bug".
One solution is to not do any reference integrity in the lists. But that opens the door to garbage in a little too wide I think.
So on second thought, maybe the SET NULL could be of use. I'd add a second non-FK column on the member assoc-table,
transfering the old entity name to it thanks to an UPDATE on entity_name, thus preserving the old name.
Then an INSERT trigger on entity could locate any (indexed) "stashed" entity names in that extra non-FK column in entity_list_member,
to retransfer the name back to the primary FK column.
I'd need to adjust the PK to a coalesce(), and ensure the two columns are mutually exclusive.
Sounds like that might work, no?
On 2/16/23 09:17, Dominique Devienne wrote: > That is where I am headed, however it will need more information to > determine whether that makes sense or not. > > > OK, I started writing SET NULL won't help, but I'll back up and try to > give more info, as requested. > Pseudo SQL at this point. > > create table entity (name text primary key, ...); > create table enity_list (name text primary key, ...); > create table entity_list_member( > list_name text not null references entity_list(name) on delete > cascade on update cascade, > entity_name text not null references entity(name) on delete cascade > on update cascade > primary key (list_name, entity_name) > ); > > Above is the current situation. When the entity is deleted, it's > implicitly deleted from all list that mention it. > Referential Integrity 101 I guess. But apparently, it's common enough > for an entity to be deleted and reloaded, > not necessarily in the same transaction, that losing the list(s) > membership on delete is considered "a bug". You have two tables with list in their name, so are rows deleted from both. Just to be clear enity_list should actually be entity_list? Also how are entity and enity_list related? > > One solution is to not do any reference integrity in the lists. But that > opens the door to garbage in a little too wide I think. > > So on second thought, maybe the SET NULL could be of use. I'd add a > second non-FK column on the member assoc-table, Are the values for the name field in entity and enity(entity)_list the same for a given entity? > transfering the old entity name to it thanks to an UPDATE on > entity_name, thus preserving the old name. How? Again how would you determine where the action started? > Then an INSERT trigger on entity could locate any (indexed) "stashed" > entity names in that extra non-FK column in entity_list_member, How would it locate it if the name that defined the FK(entity(name)) was NULL? > to retransfer the name back to the primary FK column. > I'd need to adjust the PK to a coalesce(), and ensure the two columns > are mutually exclusive. > Sounds like that might work, no? > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
You have two tables with list in their name, so are rows deleted from
both. Just to be clear enity_list should actually be entity_list?
Also how are entity and enity_list related?
I have to confess that your questions surprise me a bit.
I thought the model was pretty clear from the SQL.
We have two entities, Foo (my entity table), and another Bar (my entity_list table),
with Bar reference 0 or more Foos, recording which ones it references in an association table.
Are the values for the name field in entity and enity(entity)_list the
same for a given entity?
The name of Foo and Bar are completely independent.
> transfering the old entity name to it thanks to an UPDATE on
> entity_name, thus preserving the old name.
How?
Again how would you determine where the action started?
Deleting a Foo cascades to the _member assoc-table.
If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member.
I no longer care where the action started.
> Then an INSERT trigger on entity could locate any (indexed) "stashed"
> entity names in that extra non-FK column in entity_list_member,
How would it locate it if the name that defined the FK(entity(name)) was NULL?
In the extra non-FK column I mentioned explicitly, in the _member assoc-table.
On 2/16/2023 12:28 PM, Dominique Devienne wrote:
On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:You have two tables with list in their name, so are rows deleted from
both. Just to be clear enity_list should actually be entity_list?
Also how are entity and enity_list related?I have to confess that your questions surprise me a bit.I thought the model was pretty clear from the SQL.We have two entities, Foo (my entity table), and another Bar (my entity_list table),with Bar reference 0 or more Foos, recording which ones it references in an association table.Are the values for the name field in entity and enity(entity)_list the
same for a given entity?The name of Foo and Bar are completely independent.> transfering the old entity name to it thanks to an UPDATE on
> entity_name, thus preserving the old name.
How?
Again how would you determine where the action started?Deleting a Foo cascades to the _member assoc-table.If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member.I no longer care where the action started.> Then an INSERT trigger on entity could locate any (indexed) "stashed"
> entity names in that extra non-FK column in entity_list_member,
How would it locate it if the name that defined the FK(entity(name)) was NULL?In the extra non-FK column I mentioned explicitly, in the _member assoc-table.
Another option would be to not delete the records, but add a Deleted column and mark them as deleted.
Your CASCADE then becomes an UPDATE trigger.
Restoring the relationship would then be a simple matter of unmarking them as deleted.
I haven't tried this, but it's possible that you could hijack the DELETE trigger so the app didn't have to change how it deletes records.
If you were really insistant on the app not changing to respect the deleted flag, you could add views and read from those.
If you potentially have a lot of deleted records, and you have a time frame after which it would be unlikely they would be restored, then you could add a DeletedDate field. After a given amount of time do garbage cleanup on anything over that threshold.
We don't have that many deletes, so we just leave them. They don't show up in the app, since they are "deleted" but we have an admin mode that can ignore the deleted flag if the user chooses and they can then undelete any records.
So nothing ever gets literally deleted, but they do get archived after 18 months. And again, we have a mode where you can include archived records.
In your situation, when they undelete the parent record, it could automatically undelete the children.
Brad.
On 2/16/23 10:28, Dominique Devienne wrote: > On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > You have two tables with list in their name, so are rows deleted from > both. Just to be clear enity_list should actually be entity_list? > > Also how are entity and enity_list related? > > > I have to confess that your questions surprise me a bit. > I thought the model was pretty clear from the SQL. > We have two entities, Foo (my entity table), and another Bar (my > entity_list table), > with Bar reference 0 or more Foos, recording which ones it references in > an association table. > > Are the values for the name field in entity and enity(entity)_list the > same for a given entity? > > > The name of Foo and Bar are completely independent. > > > transfering the old entity name to it thanks to an UPDATE on > > entity_name, thus preserving the old name. > > How? > Again how would you determine where the action started? > > > Deleting a Foo cascades to the _member assoc-table. > If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member. > I no longer care where the action started. What happens if an entity_list value is deleted? Are you going to replicate the above for it to? If so something like what Brad White suggested would seem to simpler. Or, create a history table where rows deleted from entity_list_member are moved to. > > > Then an INSERT trigger on entity could locate any (indexed) > "stashed" > > entity names in that extra non-FK column in entity_list_member, > > How would it locate it if the name that defined the > FK(entity(name)) was NULL? > > > In the extra non-FK column I mentioned explicitly, in the _member > assoc-table. -- Adrian Klaver adrian.klaver@aklaver.com