3

I read the documentation for cascading delete, but one thing I'm not clear on is if it's possible to set up a database so that a child row can be shared by more than one parent and the child row only be deleted when the last referencing parent row is deleted?

(Basically I want foreign keys that act like a std::shared_ptr.)

If that's not possible with the built-in CASCADE DELETE setting, can it be done with a trigger? What would that look like?

The final option I have is that although it is possible in the library I'm writing to create this shared-reference situation, I could simply make it throw an exception when an attempt to construct such a thing takes place.

Specifically, I have a self referencing table that stores something like an abstract syntax tree. Each node has an operation and two child nodes. In my C++ program which is using the database, the objects which represent rows in this table have overloaded operators which return values that simultaneously cause rows to be created in the table. For instance "a << b" would return a temporary value "c" and the database would have a row like: (c_id, '<<', a_id, b_id). However you could follow that with a call to "a << x", in which case the database would contain two rows referencing "a".

The C++ classes are written to insert the table row in their constructor and delete the table row in their destructor. Essentially, I want the creation and destruction of temporary objects in the C++ code to be mirrored in the state of the database. But I want to suppress deleting a child row if it is still referenced by a different parent.

Dennis
  • 2,607
  • 3
  • 21
  • 28

1 Answers1

2

This is possible with a trigger.

When there are no parents with the same child left, delete the child:

CREATE TRIGGER t
AFTER DELETE ON Parent
WHEN NOT EXISTS (SELECT 1 FROM Parent WHERE ChildID = OLD.ChildID)
BEGIN
    DELETE FROM Child WHERE ID = OLD.ChildID;
END;
CL.
  • 173,858
  • 17
  • 217
  • 259