Validation on deleting record ActiveJDBC

Question

What would be the best approach if I want to put validation upon record deletion? Here is the reason why.

I have two tables, Store and Staff.

Structure of Store:

• ID
• Store Code
• Store Name

Structure of Staff:

• ID
• Staff Code
• Staff Name
• Store Code

As you can see, table store is related to table staff.

What if the user tries to delete a store that is already used on table staff? If no validation, my data would be broken. How can I prevent it?

Thanks.

Answer 1

The best way to implement it is through the referential integrity in the database, not in framework.

Structure of STORE:

• id
• code
• name

Structure of STAFF:

• id
• code
• name
• store_id

As you can see, the STAFF table "belongs" to STORE because it contains the ID of a parent record. This is a classical One-to-many association: http://javalite.io/one_to_many_associations

You can do one of two things:

1. Throw exception from DB in case you are deleting a parent without deleting a child
2. Cascade deleting a child in case a parent is deleted automatically (in DB)

A few links suggested by Google:

https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html http://www.mysqltutorial.org/mysql-on-delete-cascade/