mysql circular dependency in foreign key constraints

Question

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)?

Answer 1

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}