On Wed, Jan 22, 2025 at 2:00 AM Runxi Yu <m...@runxiyu.org> wrote: > 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.
For one thing, rows *can't* be orphaned if there's a foreign key reference. That's the whole point of creating a foreign key. As to automatically deleting children, ON DELETE CASCADE has been a feature of foreign keys for at least 20 years. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
