I have a Java application that is based (JPA, spring-data) on a relational database (PostgreSQL). This database has several tables referencing a specific table. So referencial integrity kicks in, when the user likes to delete an entry in that table.
Currently I have a complicated view joining all those referencing tables to see if there are any references. If so I have additional queries (depending on the referencing tables) to show business data to the user (and a link to the corresponding UIs) so that the user can examine the referencing data.
I don't like that solution since a) it seems to be a lot of overhead for just displaying a comprehensive error message and b) it duplicates the reference-integrity check of the database.
What I would prefer is:
- databases to have means to check referencial integrity before executing the delete
- databases to provide information which tables/rows are referencing the data (when the error kicks in)
I am pretty sure that both is not possible (but I don't understand why databases don't have those features). Correct me if I am wrong. And if there are other possibilities / best practices, please let me know.
