CODEWITHSUNDEEP
postgresql
bozo
bozo

Reputation: 946

What is the best way to delete related records in PostgreSQL, not connected by a direct foreign key?

In order to cut down on "stupid" tables (the ones which are identical for several related parent entities) we made a few generic tables.

Here is an example:

tbl_settings

Now the problem are the deletes, where it is quite easy to forget to delete e.g. the user's settings when the user is deleted.

What is the right way to handle deletes for this kind of a table in PostgreSQL?

  1. Do it in an application (e.g. when deleting an user, do a manual delete of related settings)?
  2. Do it in a database trigger on the tbl_user (and in all other parent tables)?
  3. Something else?

Upvotes: 0

Views: 907

Answers (1)

davek
davek

Reputation: 22895

If a tables relationships have meaning only in the application and upwards - i.e. it has no bearing on the referential integrity of the data in the database - you can do this in the application layer.

If "orphaned" records violate data (as opposed to business logic) relationships, then do this in the database: the safest way is probably via a trigger, though that has its disadvantages too (e.g. the likelihood of obfuscating DML errors is higher if there is a trigger action involved).

My impression from your question is that these tables are mainly there because of some business logic, in which case I would handle the deletes outisde the database, in an ORM layer, for example.

Upvotes: 1

Related Questions