mysql circular dependency in foreign key constraints
- by Flavius
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)?
