Automatic deletion of orphaned rows

Hi,

While writing a new program, I encountered the following:

I have three tables: A, B, and X. Rows in X are referenced by A and/or B
via foreign keys, one or more times. I would like to delete all orphaned
rows in X, i.e. a row in X is deleted if and only if it is no longer
referenced by any row in A or B. (When inserting these rows, I would
insert X first, then the reference in A or B, in the same transaction.)

To the best of my knowledge, there is no such functionality natively
built into PostgreSQL. Alternatives include (1) using triggers or (2)
using application logic. Both would involve locking the row in table X,
and since I don't see a native "reference count" feature in PostgreSQL,
the reference count would have to be maintained as a field in X. Both of
these alternatives could get somewhat messy. In more complex schemas
where circular references may be involved and a mark-and-sweep garbage
collector is preferred, this would be even more difficult to implement.

I therefore propose a feature, to be able to specify in a table schema
that a row should be deleted if orphaned.

--
Best regards,

Runxi Yu (they/them)
Y11 Student
YK Pao School
https://runxiyu.org