Error Cannot truncate a table referenced in a foreign key constraint mytest instance CONSTRAINT instance ibfk 1 FOREIGN KEY GroupID REFERENCES mytest mygroup ID

0 votes

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

ERROR : Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID))

My code:

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;
Aug 18, 2020 in PHP by kartik
• 37,530 points
2,551 views

1 answer to this question.

0 votes

Hello @kartik,

You cannot TRUNCATE a table that has FK constraints applied on it (TRUNCATE is not the same as DELETE).

To work around this, use either of these solutions:

Option 1:

  1. Remove constraints
  2. Perform TRUNCATE
  3. Delete manually the rows that now have references to nowhere
  4. Create constraints

Option 2: 

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

Hope it helps!!
Thank you!!

answered Aug 18, 2020 by Niroj
• 82,820 points

Related Questions In PHP

0 votes
1 answer

Error:Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF.

Hello @kartik, You're inserting values for OperationId that is an identity ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,820 points
4,407 views
0 votes
1 answer

How do I get the last inserted ID of a MySQL table in PHP?

Hello @kartik, If you're using PDO, use PDO::lastInsertId. If you're ...READ MORE

answered Oct 22, 2020 in PHP by Niroj
• 82,820 points
202 views
0 votes
1 answer

Error:Jquery - Uncaught TypeError: Cannot use 'in' operator to search for '324' in

Hello @kartik, You have a JSON string, not ...READ MORE

answered Jun 16, 2020 in PHP by Niroj
• 82,820 points
2,754 views
0 votes
1 answer

How to see indexes for a database or table in MySQL?

Hello @kartik, To see the index for a ...READ MORE

answered Aug 18, 2020 in PHP by Niroj
• 82,820 points
6,937 views
+1 vote
1 answer

How to make anchor tag with routing using Laravel?

Hey @kartik, First you have to go to ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,820 points
9,404 views
0 votes
1 answer

What is type casting and type juggling in php?

Hey, The way by which PHP can assign ...READ MORE

answered Mar 27, 2020 in PHP by Niroj
• 82,820 points
1,314 views
0 votes
2 answers

How can we create a session in PHP?

Hello, niroj. Here is my idea session_start(); $_SESSION['USERNAME'] ...READ MORE

answered Dec 7, 2020 in PHP by Famous
• 140 points
245 views
0 votes
1 answer

What is the use of $_REQUEST variable in php?

Hii @kartik, The $_REQUEST variable is used to read the ...READ MORE

answered Mar 27, 2020 in PHP by Niroj
• 82,820 points
433 views
+1 vote
1 answer

ERROR: Cannot add or update a child row: a foreign key constraint fails

Hello @kartik, You are getting this constraint check ...READ MORE

answered Aug 20, 2020 in PHP by Niroj
• 82,820 points
11,154 views
0 votes
2 answers

Error:“Cannot Connect to Server - A network-related or instance-specific error”?

Hello, The "sql server error 40" is appears mostly ...READ MORE

answered Aug 18, 2020 in PHP by Fantazma
• 140 points
2,922 views