la dépendance circulaire mysql dans les contraintes clés étrangères
etant Donné le schéma:
ce dont j'ai besoin c'est d'avoir tous les user_identities.belongs_to référence users.id.
En même temps, tous les users a un primary_identity comme sur la photo.
Cependant lorsque j'essaie d'ajouter cette référence avec ON DELETE NO ACTION ON UPDATE NO ACTION, MySQL says
#1452-impossible d'ajouter ou de mettre à jour une rangée d'enfants: une contrainte de clé étrangère échoue (
yap.#sql-a3b_1bf, CONSTRAINT#sql-a3b_1bf_ibfk_1CLÉ ÉTRANGÈRE (belongs_to) RÉFÉRENCES (id) ON DELETE NO ACTION ON UPDATE NO ACTION)
je suppose que c'est dû à la dépendance circulaire, mais comment pourrais-je le résoudre (et maintenir l'intégrité référentielle)?
6 réponses
la seule façon de résoudre cela (au moins avec les capacités limitées de MySQL) pour permettre NULL valeurs dans les deux colonnes de FK. La création d'un nouvel utilisateur avec un primaire d'identité serait alors ressembler à quelque chose comme ceci:
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;
le seul inconvénient de cette solution est que vous ne pouvez pas forcer qu'un utilisateur a une identité primaire (parce que la colonne doit être nullable).
une autre option serait de passer à un SGBD qui supporte les contraintes différées, ensuite, vous pouvez simplement insérer les deux lignes et la contrainte ne seront enregistrés au moment de la validation. Ou utiliser un SGBD où vous pouvez avoir un index partiel, alors vous pouvez utiliser la solution avec un is_primary colonne
Je ne l'implémenterais pas de cette façon.
Supprimer le champ primary_identity tableau users, et l'ajout d'un champ supplémentaire à la table user_profilesis_primary, et utilisez plutôt ceci comme indicateur d'un profil primaire
ceci empêchera D'avoir des NULL pour FKs, mais ne fait pas encore appliquer pour que le profil primaire existe -- cela doit être géré par application.
Remarque: l'autre touche (index unique) {UserID, ProfileID}Profile table et correspondant FK sur PrimaryProfile.
cette question a été posée à comment faire tomber des tables avec des clés étrangères cycliques dans MySQL à partir de la supprimer côté des choses, mais je pense que l'une des réponses est applicable ici:
SET foreign_key_checks = 0;
INSERT <user>
INSERT <user identity>
SET foreign_key_checks = 1;
faites une transaction et engagez tout d'un coup. Je n'ai pas essayé, mais il fonctionne pour les supprime, donc je ne sais pas pourquoi ça ne marcherait pas pour les insertions.
je n'ai pas utilisé, mais vous pouvez essayer INSERT IGNORE. Je ferais ces deux-là, un pour chaque table, de sorte qu'une fois les deux faits, l'intégrité référentielle soit maintenue. Si vous les faites dans une transaction, vous pouvez revenir en arrière s'il y a un problème d'insertion de la seconde.
puisque vous ignorez les contraintes avec cette fonctionnalité, vous devriez plutôt vérifier le code du programme, sinon vous pourriez vous retrouver avec des données dans votre base de données qui ignorent votre contraintes.
merci à @Mihai pour avoir souligné le problème avec ce qui précède. Une autre approche serait de désactiver les contraintes pendant que vous faites des inserts, et de les réactiver par la suite. Cependant, sur une grande table qui peut produire plus de ressources qu'est acceptable d'essayer?
le problème semble être que vous essayez de garder les informations d'identité primaires dans la table user_identities.
à la place, je vous suggère de mettre l'information utilisateur primaire (nom/courriel) dans la table des utilisateurs. N'avez pas de clé étrangère à la user_identities table.
seule clé étrangère de la table user_identities
toutes les contraintes vont maintenant fonctionner correctement car elles ne sont qu'une seule façon.
identités_utilisateurs ne peuvent être saisies que si l'utilisateur primaire (en utilisateurs de la table) est présent. De même, l'utilisateur principal ne doit pas être deletable lorsqu'il existe des identités d'enfants (dans user_identities).
vous pourriez vouloir changer le nom des tables en "primary_users" et "secondary_users" pour rendre évident ce qui se passe.
est-ce que ça va?