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
