Categories

See More
Popular Forum

MBA (4887) B.Tech (1769) Engineering (1486) Class 12 (1030) Study Abroad (1004) Computer Science and Engineering (988) Business Management Studies (865) BBA (846) Diploma (746) CAT (651) B.Com (648) B.Sc (643) JEE Mains (618) Mechanical Engineering (574) Exam (525) India (462) Career (452) All Time Q&A (439) Mass Communication (427) BCA (417) Science (384) Computers & IT (Non-Engg) (383) Medicine & Health Sciences (381) Hotel Management (373) Civil Engineering (353) MCA (349) Tuteehub Top Questions (348) Distance (340) Colleges in India (334)
See More

How to truncate a foreign key constrained table?

General Tech Bugs & Fixes

Max. 2000 characters
Replies

usr_profile.png

User

( 6 months ago )

 

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (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;

usr_profile.png

User

( 6 months ago )

While this question was asked more than 5 years ago and I don't know this facility existed in MySql back then but now if you use phpmyadmin you can simply open the database and then select the table(s) you want to truncate. At the bottom there is a drop down with many options listed. Open it and select Empty option under the heading Delete data or table. It takes you to the next page automatically where there is an option in checkbox called Enable foreign key checks. Just unselect it and press Yes button and the selected table(s) will be truncated. May be it internally runs the query suggested in user447951's answer. But it is very convenient to use from phpmyadmin interface.

what's your interest


forum_ban8_5d8c5fd7cf6f7.gif