I have a table in my database that follows the nested sets idea.
http://forums.devshed.com/mysql-help-4/innodb-foreign-keys-on-delete-alternative-action-584955.html
nha khoa.com/showthread.php?t=34569
Code:
table.eventnodes
id (int) (primary)
lft (int)
rgt (int)
player_id (int) (foreign key)
player_id refers to this table
Code:
table.players
id (int) (primary)
player_name (varchar)
The player_id can exist in the eventnodes table many times (depending on how many events the player is participating in). However if the player is removed from the database, I want him to be removed from all of his existances in the eventnodes table as well.
Normally, when I have adjacent tables I would say "on delete cascade". However, to correctly remove the player from his entries into the events, it also requires updating every lft/rgt value where applicable, as well as deleting every record in the eventnodes that belong to the player.
I would prefer to have the database structure handle this if possible. I'm wondering if theres a way to have the database automatically use a prepared statement or something of those sorts to remove the player's nodes in the eventnodes table. Or would I have to use RESTRICT instead of CASCADE and have the programmer do this manually?