Deleting a circular reference foreign key

I’m deleting data in mysql, but it doesn’t delete because a foreign key is specified, so I tried to delete data from another table. This table also had a foreign key reference to this table..

In other words, we have a circular reference situation where they refer to each other.

MariaDB [platform]> delete from users where id='25986';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`platform`.`companies`, CONSTRAINT `companies_owner_id_foreign` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`))

First I tried to delete from users, but it was rejected because of the companies foreign key (owner_id).

So we tried to delete from copanies.

MariaDB [platform]> delete from companies where id='180';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`platform`.`users`, CONSTRAINT `users_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`))

When I do, I get a message that the users’ foreign key (company_id) does not allow them to be deleted.

::: Resolve

The solution is to temporarily remove the foreign key.

You can update the company_id and owner_id by replacing them with null or other values, or you can delete them…

MariaDB [platform]> UPDATE users SET company_id = NULL WHERE company_id = '180';
MariaDB [platform]> UPDATE companies SET owner_id = NULL WHERE owner_id = '25986';

MariaDB [platform]> DELETE FROM users WHERE id='25986';
MariaDB [platform]> DELETE FROM companies WHERE id='180';

Alternatively, temporarily disable and then delete the foreign key constraint.

MariaDB [platform]> SET FOREIGN_KEY_CHECKS = 0;
MariaDB [platform]> DELETE FROM users WHERE id='25986';
MariaDB [platform]> DELETE FROM companies WHERE id='180';
MariaDB [platform]> SET FOREIGN_KEY_CHECKS = 1;

This essay was originally published on Medium and migrated to KanApp Blog.