- Blog

Keep it simple

[MySQL] Swapping ids of two Doctrine ORM entities with foreign key constraints

written on 9 September 2017
I recently had to swap the ids of two Doctrine ORM entities (pages), that were referenced by other entities (articles). Usually you don't want to do this at all. Even copying the entity contents over would be cleaner. However, I had to achieve the result with only MySQL. You can first disable the check for foreign constraints check (and re-enable it afterwards!). Then, swap the ids of the entities using a temporary id that is >= AUTO_INCREMENT to avoid UNIQUE constraint error. Afterwards, fix the references with a single UPDATE call using the SWITCH statement. The following code swaps the ids of two pages #12 and #13 and updates references in articles: ```sql SET foreign_key_checks = 0; -- Swap ids, where 999999 is >= current AUTO_INCREMENT UPDATE pages SET id=999999 WHERE id=12; UPDATE pages SET id=12 WHERE id=13; UPDATE pages SET id=13 WHERE id=999999; -- Updates references in articles with single UPDATE UPDATE articles SET page_id = CASE page_id WHEN 12 THEN 13 WHEN 13 THEN 12 END WHERE page_id IN (12,13); SET foreign_key_checks = 1; ```