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

Palmate

is the foreing key between users and user_indentities id<->id, or id<->belongs_to? – Ratel

See my edit: At the same time, every users has a primary_identity as shown in the picture. So the FK is between both, otherwise "circular dependency" in the title wouldn't make sense. – Palmate

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}

Maidenhood

I guess postgresql would be a good alternative, these things are mentioned in their documentation. – Palmate

Answer 2

I would not implement it this way.

Remove the field primary_identity from table users, and the add an additional field to table user_profiles called is_primary, and use this rather as the indicator of a primary profile

Ratel

+1 Design-wise I agree but for the sake of argument how does one handle circular references? – Silhouette

Unfortunately, this is a chicken egg situation. how can you create 1 without the other? – Ratel

Hm, if I do that, I cannot ensure that every user has exactly one primary identity. – Palmate

Answer 3

This will prevent having NULLs for FKs, but still does not enforce for primary profile to exists -- that has to be managed by application.

Note the alternate key (unique index) {UserID, ProfileID} on Profile table and matching FK on PrimaryProfile.

Trattoria

It would be easier to just add an isPrimary field to the identities table. – Rebarbative

Answer 4

The problem seems to be that you are trying to keep the primary identity information in the user_identities table.

Instead, I suggest you put the primary user info (name/email) into the users table. Do not foreign key to the user_identities table.

Only foreign key from the user_identities table

All constraints will now work ok as they are only one way.

user_identities cannot be entered unless the primary user (in table users) is present. Similarly the primary user should not be deletable where there are existing child identities (in user_identities).

You might want to change the name of the tables to "primary_users" and "secondary_users" to make it obvious what is going on.

Does that sound okay?

Discussant

Answer 5

This question was raised at How to drop tables with cyclic foreign keys in MySQL from the delete side of things, but I think that one of the answers is applicable here as well:

SET foreign_key_checks = 0;
INSERT <user>
INSERT <user identity>
SET foreign_key_checks = 1;

Make that a transaction and commit it all at once. I haven't tried it, but it works for deletes, so I don't know why it wouldn't work for inserts.

Deficient

Hmm, I wonder if you could do this without disabling fk checks if you just made it a transaction? – Deficient

No a transaction doesn't help (i.e. it was already in a transaction when I asked). – Cellular

Thats not the purpose of foreign keys. – Rebarbative

Lo nine years later, I would consider this a bad practice. Foreign key checks are there for a reason! – Deficient

Answer 6

I've not used it, but you could try INSERT IGNORE. I'd do the two of those, one for each table, such that once they are both done, referential integrity is maintaing. If you do them in a transaction, you can roll back if there is a problem inserting the second one.

Since you're ignoring constraints with this feature, you should do that check in program code instead, otherwise you may end up with data in your database that ignores your constraints.

Thanks to @Mihai for pointing out the problem with the above. Another approach would be to disable constraints whilst you do inserts, and re-enable them afterwards. However, on a large table that might produce more overhead than is acceptable - try it?

Annecorinne

INSERT IGNORE does not ignore constraints it just doesn't trigger an error. By not triggering an error it allows you to go through hundreds of inserts even if a few of them fail for some reason. This means that any INSERT IGNORE will fail silently. – Silhouette

@MihaiStancu - thanks. I'd found it through some quick web-searching for the OP, clearly I didn't read the docs well enough! – Annecorinne