deleteAll doesn't work with foreign keys to other rows in same table

Question

I have a Model class that has an attribute referencing another instance of the same Model class. Its basically a tree structure in one Model.

When I try to exectute MyModel.deleteAll() it fails because a foreign key constraint fails.

Is there someway to easily suspend this constraint for the deleteAll query?

The only workaround I've found, since I'm using mysql, is to issue a TRUNCATE statement, which mysql accepts straight away.

Thanks in advance, Evan

Exception details:

org.javalite.activejdbc.DBException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (visibledb_testing.accountabilities, CONSTRAINT accountabilities_prototype_id FOREIGN KEY (prototype_id) REFERENCES accountabilities (id)), query: DELETE FROM accountabilities

Answer 1

The Model#deleteAll() simply generates SQL DELETE FROM YOURTABLE. If you can run this on MySQL console, it will work from the model. If you are getting constraint violation, maybe you want to:

Base.exec("TRUNCATE " + MyModel.getTableName());

Alternatively, you can try http://javalite.io/delete_cascade#method-deletecascade. BE CAREFUL- this is a powerful but dangerous method, only use after reading all docs.