Re: DELETE trigger, direct or indirect?

On Thu, Feb 16, 2023 at 5:59 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 2/16/23 08:55, David G. Johnston wrote:
> > On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)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?