Getting 'Referential integrity constraint violation' when deleting domain object

Question

In my crazy universe a Room can have many chairs and chairs can 'belong' to many rooms. In grails it looks like this. Chairs should not know which room they belong to.

class Room {
    String name
    static hasMany = [chairs: Chair]
    static constraints = {
    }
}
class Chair {
    String name
    static constraints = {
    }
}

I want to delete a chair and automatically remove any references of chairs in all room domain objects that have that chair. I have succeeded, but with a fix that i dont like. In the ChairController i did the following

def deleleChair(){
    def chairToDelete = Chair.get(params.id)
    Room.findAll().each {room->
        if(room.chairs.contains(chairToDelete)){
            room.removeFromChairs(chairToDelete)
            room.save(failOnError:true)
        }
    }
    chairToDelete.delete(params.chairId)
}

Is there a hibernate config that I need to set so that it does this automatically? It seems like something very common in real world scenarios. I would not want to implement the same piece of code, when I probably decide,(crazier example) that a Car domain object can have many chairs.

I have tried to use the grails event push plugin. I had a ChairService with an action that listened for beforeDelete events in gorm.

class ChairService {
    @grails.events.Listener(topic = 'beforeDelete', namespace = "gorm")
    def handleDeletedChair(Chair chair){
         Room.findAll().each {room->
            if(room.chairs.contains(chairToDelete)){
                room.removeFromChairs(chairToDelete)
                room.save(failOnError:true, flush:true)
            }
        }
    }
}

This function is called whenever a chair delete is attempted, but when it return to the ChairController so as to do the actual chair deletion, the delete action still thinks that the reference to Room is still there and throws a

 Caused by JdbcSQLException: Referential integrity constraint    violation:"FK4ACA6A6151428364: PUBLIC.ROOM_CHAIR FOREIGN KEY(CHAIR_ID) REFERENCES PUBLIC.CHAIR(ID)"; SQL statement:

delete from chair where id=? and version=? [23503-164]

I want this logic to be separate from the Chair, the chair should never no about the rooms.

Answer 1

Because the relationship doesn't exist bidirectionally I'm not sure there is a much more elegant solution.

However, if you're are dealing with a lot of rooms/chairs I would recommend using a criteria query with projections to load up only the ids you want to delete instead of loading up all the rooms a chairs. Better yet use the executeUpdate method to do a 'bulk' delete. Doing this should also help with your errors.

grails criteria query docs...

deleting objects in grails docs...

Answer 2

A simple solution would be to use standard SQL:

import grails.events.Listener
import groovy.sql.Sql

class ChairService {
    def dataSource // autowired

    @Listener(topic='beforeDelete', namespace='gorm')
        def handleDeletedChair(Chair chairToDelete) {
        new Sql(dataSource).execute('DELETE FROM room_chair WHERE chair_id=?', [chairToDelete.id])
        return true
    }
}

As this is SQL it may be dependent on the database you are using (I tested with H2).