eliminar - foreign key mysql workbench ejemplo

¿En qué orden se procesan las restricciones ON DELETE CASCADE?

Question

Aquí hay un ejemplo de lo que estoy pasando:

CREATE TABLE Parent (id BIGINT NOT NULL,
  PRIMARY KEY (id)) ENGINE=InnoDB;

CREATE TABLE Child (id BIGINT NOT NULL,
  parentid BIGINT NOT NULL,
  PRIMARY KEY (id),
  KEY (parentid),
  CONSTRAINT fk_parent FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE) ENGINE=InnoDB;

CREATE TABLE Uncle (id BIGINT NOT NULL,
  parentid BIGINT NOT NULL,
  childid BIGINT NOT NULL,
  PRIMARY KEY (id),
  KEY (parentid),
  KEY (childid),
  CONSTRAINT fk_parent_u FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE,
  CONSTRAINT fk_child FOREIGN KEY (childid) REFERENCES Child (id)) ENGINE=InnoDB;

Observe que no hay ON DELETE CASCADE para la relación Tío-Niño; es decir, eliminar un niño no elimina a su tío (s) y viceversa.

Cuando tengo un padre y un tío con el mismo hijo, y elimino el padre, parece que InnoDB debería ser capaz de "resolverlo" y dejar que la cascada se propague por toda la familia (es decir, borrar el padre elimina al tío) y el Niño también). Sin embargo, en cambio, obtengo lo siguiente:

  ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`cascade_test/uncle`, CONSTRAINT `fk_child` FOREIGN KEY (`childid`) REFERENCES `child` (`id`))

InnoDB está tratando de eliminar en cascada al niño antes del tío (s) que se refieren a él.

¿Me estoy perdiendo de algo? ¿Se supone que esto va a fallar por alguna razón que no entiendo? ¿O hay algún truco para hacerlo funcionar (o es un error en MySQL)?

Answer 1

En el caso más simple, ¿qué sucede si se borra un registro de Child y tiene un Tío que hace referencia? Eso no se especifica, por lo que las restricciones fallan para eso de todos modos.

Si eliminar un elemento secundario no elimina sus tíos, ¿qué sucede en su lugar? Uncle.childid no puede ser nulo.

Lo que quieres es una de estas tres cosas:

1. Uncle.childid puede ser nulo y desea ON DELETE SET NULL para childid.
2. Uncle.childid no puede ser nulo, y usted quiere ON DELETE CASCADE para childid.
3. Childid no pertenece a Uncle, y desea una relación ChildsUncle con ON DELETE restricciones de clave externa de CASCADE tanto para Niño como para Tío. Uncleid sería una clave candidata para esa relación (es decir, debería ser única).

Answer 2

el diseño está todo mal. Debería tener una sola tabla, con una relación padre-hijo (literalmente). Entonces puedes descubrir tíos (y tías) con una consulta

select id from persons where -find all children of the grandparents  parent id in (  select parentid from persons --find the grandparents  where id in (  select parentid from persons --find the parents  where id=THECHILD) )  minus --and take out the child''s parents  select parentid from persons  where id=THECHILD 

Answer 3

La eliminación de los padres desencadena la eliminación de elementos secundarios como usted indicó y no sé por qué va a la tabla secundaria antes de la tabla de tíos. Me imagino que tendrías que mirar el código dbms para estar seguro, pero estoy seguro de que hay un algoritmo que selecciona las tablas a las que se debe conectar primero.

El sistema realmente no ''descifra'' las cosas de la forma que implica aquí y solo sigue sus reglas de restricción. El problema es el esquema que creó en el que encuentra una restricción que no lo dejará pasar más lejos.

Veo lo que dices ... si llega primero a la mesa del tío, se eliminaría el registro y luego se eliminaría el elemento secundario (y no se presionaría la cascada del tío desde la eliminación del elemento secundario). Pero aún así, no creo que un esquema se configure para confiar en ese tipo de comportamiento en la realidad. Creo que la única forma de saber con certeza qué está pasando es buscar en el código o buscar uno de los programadores de mysql / postgresql aquí para decir cómo procesa las restricciones fk.

Answer 4

@Matt Solnit, en primer lugar, esta es una buena pregunta y, por lo que sé, cuando se va a eliminar un registro de Parent, innodb primero intenta identificar qué otras tablas contienen referencias para que pueda eliminar el registro de ellos como bien. En su caso es Child table y Uncle table, ahora parece que en este caso decide borrar primero el registro de Child table y así repite el mismo proceso para Child y eventualmente falla como Uncle guarda referencia a Child table pero tampoco "ON" BORRAR CASCADA "ni" ON DELETE SET NULL "se especifica para fk_child FK en la tabla Uncle. Sin embargo, parece que si innodb primero intenta eliminar el registro de la tabla Uncle, la eliminación debería haber sucedido sin problemas. Bueno, después de pensarlo dos veces, creo que, dado que innodb sigue el modelo ACI, elige Child over Uncle para comenzar el proceso de borrado, ya que si comienza con Uncle incluso la eliminación en Child podría haber fallado. Por ejemplo, suponga una tabla Friend que tenga la clave fk_child (similar al Tío) sin ON DELETE CASCADE, ahora esto aún habría causado que toda la transacción fallara y, por lo tanto, este comportamiento me parece correcto. En otras palabras, innodb comienza con una tabla que puede causar una posible falla en la transacción, pero esa es mi teoría, en realidad podría ser una historia completamente diferente. :)