integrity constraint violation when deleting user object that uses spring-security-core classes

Question

I use the spring-security-core plugin in my application. My user class extends SecUser and nothing else is done that could be out of the ordinary When trying to delete a user i get the following error

| Error 2012-01-02 19:54:57,277 ["http-bio-8080"-exec-10] ERROR util.JDBCExceptionReporter - Referential integrity constraint violation: "FK6630E2AB3FFA32A: PUBLIC.SEC_USER_SEC_ROLE FOREIGN KEY(SEC_USER_ID) REFERENCES PUBLIC.SEC_USER(ID)"; SQL statement: delete from sec_user where id=? and version=? [23003-147]
| Error 2012-01-02 19:54:57,294 ["http-bio-8080"-exec-10] ERROR events.PatchedDefaultFlushEventListener - Could not synchronize database state with session

Caused by JdbcSQLException: Referential integrity constraint violation: "FK6630E2AB3FFA32A: PUBLIC.SEC_USER_SEC_ROLE FOREIGN KEY(SEC_USER_ID) REFERENCES PUBLIC.SEC_USER(ID)"; SQL statement: delete from sec_user where id=? and version=? [23003-147]

any idea why i get the integrtiy constraint violations

Answer 1

The reason that you are getting the integrity violation exception is because of the fact that spring security creates a junction table SEC_USER_SEC_ROLE between the SEC_USER and SEC_ROLE tables ( a user can have multiple roles and vice-versa). To delete the user object you can use the following code:

    Collection<SecUserSecRole> userRoles = SecUserSecRole.findAllBySecUser(user);
    userRoles*.delete();
    user.delete();