alerting that an object cannot be deleted (due to constraints)

Question

Assume an application with a rich domain model with many classes (e.g School, Classroom, Teacher, Student, Course, Exam, Submission, ...) linking to each other. Model and links are mapped to the database which uses appropriate FKs and constraints (without cascade delete). In the admin panel the user has delete buttons next to each object.

Attempting to delete an object has one of the following two outcomes:

• the object isn't being referenced from any other object, so it is deleted
• the object is being referenced by at least one other object so it cannot be deleted. An alert is shown to the user.

I've got two ways to implement this:

1. prior to executing the sql delete, do whatever queries are necessary to discover whether this object can be deleted. If it cannot, then alert user.
2. go ahead and execute the sql delete and if that fails (due to the rdbms constraints) catch the sql exception and alert the user that it cannot be deleted.

Both ways work well. The first way allows me to give the user a detailed reason why the object cannot be deleted (e.g it is being referenced by 2 Courses and 1 Classroom). The second way allows me to solve the whole problem by not writing any constraints checking code and rely on the solid (and existing) implementation of the db.

Is there a reason why I should definitely choose one over the other?

• Kilian Foth: You've already enumerated the alternatives and their pro's and con's yourself. The only additional information needed to decide is how important each factor is to you, and you know that better than we do.
• cherouvim: Thanks. I was concerned in case there is something important that I don't know about any of the two ways.
• Bart van Ingen Schenau: To give comprehensive feedback, you could also first try to delete and if that fails, perform the queries to find out where the object is still referenced.

Answer 1

I regularly use approach two. Downside is that tests require a database. Some will argue that depending on a database is not 'unit-testing'. Indeed.

The problem with this critique is that it considers unit tests as the One Size Fits All. Clearly unit tests are not tailored to be used with foreign systems. That does not mean you should not rely on those systems ; or worst still, not test their interaction with your code. But anyways, you will have to test the case of database foreign key violations because it would be a Bad Thing not to have foreign key constraints, and a Bad Thing not to test them.

To a small extent, you can unit test foreign systems with mocks throwing the expected exceptions... if you know what to expect ! (This may not be the case since the problem statement sounds very schoolish).

In your case, the problem sample seems so small that the extra round trip of solution 1 does not really matter and you need not rely on the database for performance. But solution 1 would require a little extra coding from you, while foreign key constraints are already enforced by the database.

Be smart, be lazy.

Answer 2

One reason why I wouldn't use 2nd option : testability. You simply cannot test case, which depends on SQL server throwing an exception. But you can easily test case, where you request specific data and behave based on those data.

Testability is one of the main reasons why many developers move away from having logic inside DB server or relying on DB-related behavior and putting all behavior inside the application (server) itself.

• Tulains Córdova: What if two separate applications with different validation behavior use the same database ?
• Euphoric: @user61852 They don't. If multiple clients want to talk to DB, then they have to go through (web)service first. This ensures same validation is applied to data.
• Tulains Córdova: How do you make pgadmin to use a web service ?

Answer 3

I'd also go for option 2. Speaking of testability concern, this obviously cannot be really unit tested, but, matter of fact, it shouldn't be. Such tests are integration tests, and should be executed in a real (albeit controlled) environment, ideally as a part of continuous integration (CI), where you would test every change in DB schema and every change of application's source code against each other.

Why would I prefer option 2? Well, because one should use right tools to do the work. Keeping up the data integrity is one of the key points of RDBMS, and not using it is just like not picking up the money laying on the ground.

Answer 4

First I want to make clear this:

• Database must use FK, PK and check constraints, regardless of the application doing preventive validations or not.

Having made that clear,

Advantages of preventive validations

• Less page reloads needed
• Preventive validations allows to disable delete button accordingly
• More user friendly

Disadvantages of preventive validations

• More complex presentation logic
• More code to maintain
• More developing time

In my case I tend to favor code simplicity avobe all else, so I'd choose option 2, but that doesn't seem to be your case so I recommend you go with option 1.

What you should never, ever do is having the database NOT enforce PK, FK or check constraints and rely only in the application for data consistency.