¿Cómo truncar una tabla restringida de clave externa?

¿Por qué no funciona un TRUNCATE en mygroup ? Aunque tengo ON DELETE CASCADE SET obtengo:

ERROR 1701 (42000): No se puede truncar una tabla a la que se hace referencia en una restricción de clave externa ( instance mytest . CONSTRAINT instance_ibfk_1 FOREIGN KEY ( GroupID ) REFERENCIAS mytest . mygroup ( ID ))

 drop database mytest; create database mytest; use mytest; CREATE TABLE mygroup ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE instance ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, GroupID INT NOT NULL, DateTime DATETIME DEFAULT NULL, FOREIGN KEY (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE, UNIQUE(GroupID) ) ENGINE=InnoDB; 

No puede TRUNCATE una tabla que tiene restricciones de FK aplicadas ( TRUNCATE no es lo mismo que DELETE ).

Para solucionar esto, use cualquiera de estas soluciones. Ambos presentan riesgos de dañar la integridad de los datos.

Opción 1:

  1. Eliminar restricciones
  2. Realizar TRUNCATE
  3. Eliminar manualmente las filas que ahora tienen referencias a la nada
  4. Crear restricciones

Opción 2: sugerido por el usuario447951 en su respuesta

 SET FOREIGN_KEY_CHECKS = 0; TRUNCATE table $table_name; SET FOREIGN_KEY_CHECKS = 1; 

Sí tu puedes:

 SET FOREIGN_KEY_CHECKS = 0; TRUNCATE table1; TRUNCATE table2; SET FOREIGN_KEY_CHECKS = 1; 

Con estas afirmaciones, corre el riesgo de dejar filas en sus tablas que no se adhieren a las restricciones FOREIGN KEY .

Simplemente lo haría con:

 DELETE FROM mytest.instance; ALTER TABLE mytest.instance AUTO_INCREMENT = 1; 

Según la documentación de mysql , TRUNCATE no se puede usar en tablas con relaciones de claves foráneas. No hay una alternativa completa AFAIK.

Dejar caer el contraint todavía no invoca ON DELETE y ON UPDATE. La única solución en la que puedo pensar ATM es:

  • eliminar todas las filas, soltar las claves externas, truncar, volver a crear las claves
  • eliminar todas las filas, restablecer auto_increment (si se usa)

Parecería que TRUNCATE en MySQL aún no es una característica completa (tampoco invoca los desencadenantes). Ver comentario

tu puedes hacer

 DELETE FROM `mytable` WHERE `id` > 0 

Si bien esta pregunta fue hecha hace más de 5 años y no sé si esta instalación existía en MySql en ese momento, pero ahora si usa phpmyadmin simplemente puede abrir la base de datos y luego seleccionar la (s) tabla (s) que desea truncar. En la parte inferior hay un menú desplegable con muchas opciones en la lista. Ábrelo y selecciona la opción Vaciar bajo el encabezado Eliminar datos o tabla . Le lleva a la siguiente página automáticamente donde hay una opción en la checkbox llamada Habilitar comprobaciones de claves externas. Simplemente deseleccione y presione el botón Sí y la (s) tabla (s) seleccionada (s) se truncarán. Puede ser que ejecute internamente la consulta sugerida en la respuesta del usuario447951. Pero es muy conveniente usarlo desde la interfaz de phpmyadmin.

La respuesta es de hecho la proporcionada por zerkms , como se indica en la Opción 1 :

Opción 1 : que no daña el daño a la integridad de los datos:

  1. Eliminar restricciones
  2. Realizar TRUNCATE
  3. Eliminar manualmente las filas que ahora tienen referencias a la nada
  4. Crear restricciones

La parte complicada es Eliminar restricciones , por lo que quiero decirle cómo, en caso de que alguien necesite saber cómo hacerlo:

  1. Ejecute la consulta SHOW CREATE TABLE

    para ver cuál es el nombre de su FOREIGN KEY (marco rojo en la imagen de abajo):

    enter image description here

  2. Ejecute ALTER TABLE

    DROP FOREIGN KEY

    . Esto eliminará la restricción de clave externa.

  3. Elimine el índice asociado (a través de la página de estructura de tabla) y listo.

para volver a crear claves externas:

 ALTER TABLE  ADD FOREIGN KEY () REFERENCES ();

Obtener el antiguo estado de comprobación de clave externa y el modo sql son la mejor forma de truncar / soltar la tabla como lo hace Mysql Workbench mientras se sincroniza el modelo con la base de datos.

 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;` SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; DROP TABLE TABLE_NAME; TRUNCATE TABLE_NAME; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; 

Si el motor de base de datos para tablas difiere, obtendrá este error, así que cámbielo a InnoDB

 ALTER TABLE my_table ENGINE = InnoDB;