mysql circular dependency in foreign key constraints

database-design, mysql, referential-integrity, sql

Solution

The only way to solve this (at least with the limited capabilities of MySQL) to allow `NULL` values in both FK columns. Creating a new user with a primary identity would then look something like this:

insert into users (id, primary_identity)
values (1, null);

insert into identities (id, name, belongs_to)
values (1, 'foobar', 1);

update users 
  set primary_identity = 1
where id = 1;

commit;

The only drawback of this solution is that you cannot force that a user has a primary identity (because the column needs to be nullable).

Another option would be to change to a DBMS that supports deferred constraints, then you can just insert the two rows and the constraint will only be checked at commit time. Or use a DBMS where you can have a partial index, then you could use the solution with an `is_primary` column

Problem

Given the schema: What I need is having every `user_identities.belongs_to` reference an `users.id`. At the same time, every `users` has a `primary_identity` as shown in the picture. However when I try to add this reference with `ON DELETE NO ACTION ON UPDATE NO ACTION`, MySQL says #1452 - Cannot add or update a child row: a foreign key constraint fails (`yap`.`#sql-a3b_1bf`, CONSTRAINT `#sql-a3b_1bf_ibfk_1` FOREIGN KEY (`belongs_to`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) I suspect this is due to the circular dependency, but how could I solve it (and maintain referential integrity)?

Original source