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@xxxxxxxxxxx
