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.klaver@aklaver.com