[MySQL] Multiple foreign key cascade delete

事情是這樣的, 遇到了一個 table B, 他分別 foreign key 到另外兩個 table A 和 C, 我在 B to C 的 foreign key 上面設定了 on delete cascade, 但是我希望 B 因為 C 被刪除也跟著被 cascade delete 的時候, A 也可以一起被刪除.

除了 trigger 之外, 我想不到什麼其他更好的作法, 所以上 stackoverflow 發問一下: http://stackoverflow.com/questions/22341402/mysql-table-multi-foreign-key-cascade, 下面有人提到 DB 的 foreign key 並沒有一對一的限制, 所以希望作到這件事其實有點違背邏輯. 於是乎只好尋求 trigger 一解.

正當我要定義一個 ON DELETE B 的 trigger 去殺掉 A 的時候, 發現由 C cascade delete 驅動 B 刪除, 並不會觸發該 trigger, 上網一查才發現, MySQL 的手冊上寫著 Triggers currently are not activated by foreign key actions.

目前想到的解決方式, 就是原本的 on delete cascade 部份也改成用 trigger...

-- EOF --

[JPA] Use refresh to get updated data after database trigger

Today I met the issue about EntityManager cache issue.

Let's say that I have 2 tables comment and commant_like. I have a database trigger which increment the field like_count of comment when inserting one new comment_like record.

Below is the orignal code looks like:

In my expectation, the save call will make the database trigger to update the like_count in the comment, but it doesn't.

public void addLikeOnComment(...) {

    commentLikeDao.save(newEntity);
    
    Comment comment = commentDao.find(id);    
    
}

After digging into the code, I found the find has been called somewhere before addLikeComment. so the entity manager will use the cached version message.

public void doWork(...) {

    commentDao.find(id);

    addLikeOnComment(...);

}

The solution is simple. Use the entity manager refresh, to load data from database instead of the cached version

public void addLikeOnComment(...) {

    commentLikeDao.save(newEntity);
    
    Comment comment = commentDao.find(id); 
    commentDao.getEntityManager().refresh(comment)
    
}

-- EOF --