Check if object is referenced to prevent soft-deleting without modifying database

Question

As you can see I am using soft/logical deletion on my system:

My entities:

@Where(clause = "deleted='false'")
public class Person { 
  //...
}

My services:

@Override
public ServiceResponse DeletePerson (Person entity) {
    ServiceResponse sr = new ServiceResponse<>();

    try {
        sr = ValidateDeletePerson(entity); //Business logic treatment
        if (sr.hasError())
            return sr;

        Person dbEntity = GetPerson(entity.getPersonID());
        dbEntity.setDeleted(true);
        _repository.save(dbEntity);

    } catch (Exception ex) {
        sr.getErrors().add(new ServiceError(ex));
    }

    return sr;
}

When the user try to delete an object - actually only the boolean deleted is switched to true on the DB as I demonstrate above -, I want to check if the object is being referenced to another before doing this logical deletion.

So, if the object is being used/referenced by other, I want to catch this action as an error, or another similar treatment to prevent the deletion.

Example, If a Person with ID 5 is attached to a Student, the user cannot delete this person.

What is the "best practice" to prevent it?

Important EDIT:

1) Person is referenced in N tables, not only on Student, so I am investigating a generic way to achieve it (Maybe you can tell hibernate to check it?)

2) The idea is to do it programatically, avoiding modifications on DB.

Well, one quick and dirty solution is to physically delete the entity, flush the context, and reinsert it again with `deleted` equal to `true`. Out of all the alternatives, this solution might actually have sufferable performance penalty, but it also depends on the data model — crizzis, Apr 25 '18 at 00:32
Agreed provided that the model actually has foreign key constraints in place. However, typically soft deletes are used when other records must be preserved, for example old audit logs, preventing the real delete from succeeding. But if it does work it is an interesting option! — ewramner, Apr 25 '18 at 06:05

Bartosz Bilicki · Accepted Answer · 2018-04-24T20:11:38.627

You could use check constraints at database level. Details are DBMS-specific but general idea for constrain is

CHECK ((select count(*) from studens 
                         where studens.id= person.id and person.deleted=false) = 0)

Other solution - archive table

You could avoid this problem by deleting records and moving deleted records to person_archive table (database trigger could do that for you). With this solution foreign key will be able to protect data integrity. If your person table is big this solution may be more efficient too, because database will have to read less data. I would use person_archive table, unless you need to easily restore deleted records from UI With deleted flag, restoring is just flipping flag. With archive table, restoring is more work:

select from archive
insert into data table
delete from archive

If you cannot modify database

If database cannot be changed then those checks must be done inside your DAO classes (you need to invoke queries for all related entities). Better be sure that all database accesses goes by those classes, otherwise (if somebody uses direct SQL) you may end up with database invariant not holding true.

Your approach is good, but as I've just eddited I can't touch the DB — Pablo De Luca, Apr 24 '18 at 20:08
'can't touch the DB'. Thats unfortunate (risky and fragile) because somebody will be able to break the contract when using SQL directly, or from other applications. — Bartosz Bilicki, Apr 24 '18 at 20:13
Yes, I know that the best is to do it on the DB, but these are the directives — Pablo De Luca, Apr 24 '18 at 20:20

score 1 · Answer 2 · answered Apr 24 '18 at 19:37

1

You should check this reference through database query.

For e.g. If Person and Student table is there. If you are deleting an entry on person table with ID=5, you can check first reference in student table using query like

select count(1) from student where person_id = 5

If result > 0 then its error in your case, otherwise proceed with deletion

answered Apr 24 '18 at 19:37

Pranav Maniar

1,545
10
22

As I've just eddited, Person is referenced on N tables. But your approach is good for my question before this edition. – Pablo De Luca Apr 24 '18 at 19:50

score 1 · Answer 3 · answered Apr 24 '18 at 20:08

The best practice is to do this in the database, no argument about it. Referential integrity handled in application code always breaks at some point, if only because people have a tendency to access the tables from several applications and by hand.

Programmatically you can check by building a query that checks if something exists that references person. I would use JPA QL or HQL or even Criteria, no need to drop down to SQL. One query should be enough (combine with OR). The trick is to find all the entities and fields to include in the query. For one case it is straightforward, handcraft the query. You probably know what references Person, if you don't you can search in the IDE or check the constraints in the database. In the general case you want to generate this code or build it on-the-fly.

For building on the fly, EntityManager.getMetamodel() will give you all entities, so you can go through their attributes and see if there are potential references to the entity you are working with. Cumbersome.

Generating code based on the annotated classes can be tricky, but if you know what classes to inspect (and if you list them in your persistence unit you do) it becomes much easier. Go through the listed classes, read the annotations and generate the validation code.

If you really are determined to do this in a general way programmatically I would start by handcrafting the first two implementations. Then I would use that as a basis for generated code.

Check if object is referenced to prevent soft-deleting without modifying database

3 Answers3

Linked