Re: DELETE trigger, direct or indirect?

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(dot)klaver(at)aklaver(dot)com