Evan Martin wrote > Hi All, > > I have a database schema where if row is deleted from one table the rows > it references in another table should also be deleted, unless still > referenced by something else. > > Eg. Table A has foreign key to table B. When I delete a row from A I > also want to delete the referenced row in B, unless it's still > referenced by something else (which may be another row in A or in a > completely different table C). > > The way I currently do this is to have an AFTER DELETE FOR EACH ROW > trigger on A, which attempts to delete the row in B, but catches and > ignores a foreign_key_violation exception. This works (the foreign keys > don't have ON DELETE CASCADE), but it's slow when deleting many rows. > > A single query that deletes all the referenced rows in B, if they're not > referenced by A or C, is much faster. The problem with that approach is > it's error-prone to write and maintain. There may be many other tables > that reference B and if they ever change this query has to be updated, > which is a maintenance nightmare. I could try to auto-generate the SQL > for it by finding foreign key constraints referencing B in > information_schema, but that's not a trivial exercise. It also falls > short in a more complicated scenario where I want to delete rows in > multiple tables (A1, A2, ...) that may all reference B. > > Is there an easier way to do this? Postgres obviously knows about all > the foreign keys, so is there any way to get it to do the checking for > me? I mean a way that works in bulk, not one row at a time. > > Regards, > > Evan Basically - if there are no foreign references to a given primary key that key should be deleted? First question I'd ask is: What harm is there in not deleting the row in that situation? If you can use an FK to ensure that such a row cannot be removed if there is at least one related row then aggressively scanning for an removing rows in a batch operation - much like vacuum works - seems to be a desirable option to consider. I do not believe PostgreSQL has the ability to expose the data you need; or to perform such a task in bulk. What would be required is some kind of internal storage of a "reference counter" - but the current FK check basically runs that same single-row lookup check that you would have to code. I'd suggest you do the dynamic, catalog-driven, query. You can add additional infrastructure if you want to make the routine safer but assuming you can rely on foreign keys to avoid invalid deletions that is mostly a luxury. You will know quite quickly if someone introduces a schema change that brakes your code - ideally in your development and/or staging environment. Thus the decision is whether to enforce the rule by adding lots of triggers OR whether to periodically perform a cleanup of the database based upon those rules. Note you can make use of an "after statement" trigger instead of "after each row" if that proves to be more efficient based upon your usage. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Efficiently-delete-rows-not-referenced-by-a-foreign-key-tp5794440p5794445.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
