Comment tronquer une table à clé étrangère?

pourquoi un tronqué sur mygroup ne fonctionne-t-il pas? Même si j'ai ON DELETE CASCADE SET je comprends:

ERREUR 1701 (42000): Ne peut pas tronquer une table référencée dans une contrainte de clé étrangère ( mytest . instance ," CONSTRAINT instance_ibfk_1 FOREIGN KEY ( GroupID ) REFERENCES 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;
509
demandé sur Waqleh 2011-03-28 01:57:42

9 réponses

Vous ne pouvez pas TRUNCATE une table qui a FK contraintes appliquées sur celui-ci ( TRUNCATE n'est pas la même chose que DELETE ).

Pour contourner ce problème, utilisez l'une de ces solutions. Les deux présentent des risques d'atteinte à l'intégrité des données.

Option 1:

  1. supprimer les contraintes
  2. Exécuter TRUNCATE
  3. Supprimer manuellement les lignes ont maintenant des références à nulle part
  4. créer des contraintes

Option 2: suggérée par user447951 dans leur réponse

" 
SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;
720
répondu zerkms 2017-08-22 04:29:13

Oui, vous pouvez: 

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

avec ces énoncés, vous risquez de laisser entrer des lignes dans vos tables qui n'adhèrent pas aux contraintes FOREIGN KEY .

1113
répondu user447951 2015-12-29 05:37:32

je le ferais simplement avec: 

DELETE FROM mytest.instance;
ALTER TABLE mytest.instance AUTO_INCREMENT = 1;
121
répondu George Garchagudashvili 2015-12-29 05:36:47

comme pour documentation mysql , TRUNCATE ne peut pas être utilisé sur les tables avec des relations de clé étrangères. Il n'y a pas D'alternative complète AFAIK.

la suppression du contraint n'invoque toujours pas la suppression ON et la mise à jour. La seule solution à laquelle je ne peux pas penser est à l'un ou l'autre:

  • supprimer toutes les lignes, laisser tomber les clés étrangères, tronquer, recréer les clés
  • supprimer toutes les lignes, réinitialiser auto_ incrément (si utilisé)

il semblerait que Truncate dans MySQL n'est pas encore une fonctionnalité complète (il n'invoque pas non plus de déclencheurs). Voir le commentaire

10
répondu Omer Sabic 2015-12-09 14:25:15

vous pouvez le faire 

DELETE FROM `mytable` WHERE `id` > 0
10
répondu Ali Sadran 2017-05-13 13:20:27

alors que cette question a été posée il y a plus de 5 ans et je ne sais pas si cette facilité existait dans MySql à l'époque, mais maintenant si vous utilisez phpmyadmin vous pouvez simplement ouvrir la base de données et ensuite sélectionner la table(s) que vous voulez tronquer. En bas il y a un drop down avec de nombreuses options énumérées. Ouvrez - le et sélectionnez l'option vide sous le titre supprimer les données ou le tableau . Il vous amène à la page suivante automatiquement où il y a une option dans la case Activer le contrôle des clefs étrangères. Juste décochez-la et appuyez sur le bouton Oui et la table sélectionnée(s) sera tronqué. Peut-être qu'il exécute en interne la requête suggérée dans la réponse de user447951. Mais il est très pratique à utiliser à partir de l'interface phpmyadmin.

6
répondu Rolen Koh 2017-01-12 03:47:14

la réponse est en effet celle fournie par zerkms , comme indiqué sur Option 1 :

Option 1 : qui ne risque pas d'endommager l'intégrité des données:

  1. supprimer les contraintes
  2. effectuer troncature
  3. supprimer manuellement les lignes qui ont références à nulle part
  4. créer des contraintes

la partie délicate est suppression des contraintes , donc je veux vous dire comment, au cas où quelqu'un a besoin de savoir comment faire cela:

  1. Exécuter SHOW CREATE TABLE <Table Name> requête pour voir qu'est-ce que votre CLÉ ÉTRANGÈRE 's nom (cadre Rouge dans l'image ci-dessous):

    enter image description here

  2. Exécuter ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign Key Name> . Cela supprimera la contrainte de la clé étrangère.

  3. supprimez le Index (par la page de structure de tableau), et vous êtes fait.

pour re-créer des clés étrangères: 

ALTER TABLE <Table Name>
ADD FOREIGN KEY (<Field Name>) REFERENCES <Foreign Table Name>(<Field Name>);
3
répondu Trix 2018-02-14 10:18:25

obtenir l'ancien état de vérification de clé étrangère et le mode sql sont la meilleure façon de tronquer / laisser tomber la table comme Mysql Workbench faire tout en synchronisant le modèle à la base de données. 

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;
0
répondu Vijay Arun 2017-12-15 19:02:25

si le moteur de base de données pour les tables diffèrent, vous obtiendrez cette erreur alors changez-les en InnoDB 

ALTER TABLE my_table ENGINE = InnoDB;
0
répondu sajad abasi 2018-08-20 21:14:36