dependencia circular mysql en restricciones de key externa

Dado el esquema:

enter image description here

Lo que necesito es tener cada user_identities.belongs_to reference a un users.id .

Al mismo time, todos los users tienen una primary_identity como se muestra en la image.

Sin embargo, cuando trato de agregar esta reference con ON DELETE NO ACTION ON UPDATE NO ACTION , dice MySQL

# 1452 – No se puede agregar o actualizar una fila secundaria: falla una restricción de key externa ( yap . #sql-a3b_1bf , #sql-a3b_1bf #sql-a3b_1bf_ibfk_1 CLAVE belongs_to ( belongs_to ) REFERENCIAS users ( id ) AL BORRAR NO HAY ACTUACIÓN AL ACTUALIZAR NO HAY MEDIDAS)

Sospecho que esto se debe a la dependencia circular, pero ¿cómo podría resolverlo ( y mantener la integridad referencel)?

La única forma de resolver esto (al less con las capacidades limitadas de MySQL) para permitir valores NULL en ambas columnas FK. Crear un nuevo usuario con una identidad principal se vería así:

 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; 

El único inconveniente de esta solución es que no se puede forzar que un usuario tenga una identidad primaria (porque la columna debe ser nulable).

Otra opción sería cambiar a un DBMS que admita restricciones diferidas, luego puede simplemente insert las dos filas y la restricción solo se comprobará en el momento de la confirmación. O use un DBMS donde pueda tener un índice parcial, luego podría usar la solución con una columna is_primary

No lo implementaría de esta manera.

Elimine el campo primary_identity de los users tabla, y agregue un campo adicional a la tabla user_profiles llamado is_primary , y utilícelo como indicador de un perfil primario

Esto evitará tener valores NULL para FKs , pero aún no FKs que exista un perfil primario, que debe ser administrado por la aplicación.

Tenga en count la key alternativa (índice único) {UserID, ProfileID} de Profile {UserID, ProfileID} en la tabla de Profile y FK correspondiente en PrimaryProfile .

enter image description here

Esta pregunta se planteó en Cómo quitar tablas con keys externas cíclicas en MySQL desde el lado de la eliminación, pero creo que una de las respuestas es aplicable aquí también:

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

Haga una transacción y comprométalo todo de una vez. No lo he probado, pero funciona para las eliminaciones, así que no sé por qué no funcionaría para las inserciones.

No lo he usado, pero podrías probar INSERT IGNORE . Haría los dos, uno para cada table, de tal manera que una vez que ambos hayan terminado, la integridad referencel se mantendrá. Si los hace en una transacción, puede retroceder si hay un problema al insert el segundo.

Como ignoras las limitaciones de esta function, deberías verificar el código del progtwig; de lo contrario, puedes terminar con datos en tu database que ignoran tus restricciones.

Gracias a @Mihai por señalar el problema con lo anterior. Otro enfoque sería desactivar las restricciones mientras inserta, y volver a habilitarlas después. Sin embargo, en una table grande que podría producir más sobrecarga de lo aceptable, pruébalo.

El problema parece ser que intentas mantener la información de identidad principal en la tabla de identidades_de_usuario.

En cambio, sugiero que coloque la información principal del usuario (nombre / correo electrónico) en la tabla de usuarios. No key externa a la tabla user_identities.

Solo la key externa de la tabla user_identities

Todas las restricciones ahora funcionarán bien ya que solo son de una sola manera.

user_identities no se pueden ingresar a less que el usuario principal (en los usuarios de la tabla) esté presente. Del mismo modo, el usuario principal no debe ser eliminable cuando existen identidades hijo (en user_identities).

Es posible que desee cambiar el nombre de las tablas a "primary_users" y "secondary_users" para que sea obvio lo que está sucediendo.

Suena bien?