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.

Answer 1

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:

1. select from archive
2. insert into data table
3. 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.