Consider following relationship on MySQL database.
+--------------+ +---------------+
| Child | | Parent |
+--------------+ 1 +---------------+
| child_id (PK)| 1 |-----| parent_id (PK)|
| parent_id(FK)|-------------| | name |
| name | | address |
| age | +---------------+
| gender |
| grade |
+--------------+
I want to update a record where parent_id = 100. My requirement is to modify to parent_id=101. Assume there is no records as 101. This operation need to update child.parent_id = 101 as well so the original relation remains.
My current approach is, roughly
START TRANSACTION;
SET FOREIGN_KEY_CHECKS = 0;
UPDATE Child SET parent_id=101 WHERE parent_id=100;
UPDATE Parent SET parent_id=101 WHERE parent_id=100;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
- Can this be problematic in production environment where thousands of writes per second done?
- Is there a proper way I can achieve this without disabling foreign key checks?
