How to Truncate a MySQL Table With Foreign Key Constrains

Usually, we can use the MySQL truncate command when we want to remove all the data in a MySQL table without making any change to its structure or table. The truncate command is quite similar to the MySQL delete command. However, there are some differences. One such significant difference is, MySQL truncate command is efficient when compared with the MySQL delete command. Unlike the MySQL delete command, it does not remove rows one by one, instead, it removes the entire table and recreates it. Because of that, MySQL truncate command does not return the number of removed rows.

Likewise the MySQL delete command, MySQL does not allow truncating a table when there is a foreign key reference between table data. So let’s create some sample tables and add some data to them to demonstrate how to use the MySQL truncate command with foreign keys.

TLDR: If you already know basics like connecting to MySQL server, creating databases and running MySQL queries,.. etc, just directly go to the MySQL truncate section of this post.

Database And Table Preparation

First, we need to create a database and create some sample tables. For this post, we create two tables called “users” and “projects”. The users table has id, first name and last name columns. The projects table has id, user id and name of the project. You can simply use either phpMyAdmin or MySQL CLI or something else you may already be familiar with to run MySQL queries.

Connect And Run MySQL Queries Using phpMyAdmin

If you are using phpMyAdmin, first, login to it and create a new database.

After that navigate to the “SQL” tab. Here you can type all the MySQL queries and run them by pressing the “Go” button. At this moment, make sure that you have selected the intended database.

Connect And Run MySQL Queries Using MySQL CLI

If you are going to run SQL queries using the MySQL CLI, first, you need to connect to the MySQL server using your MySQL server username and password.

mysql -u root -p

Select the intended database using the MySQL use command.

use sample_db;

Now you are ready and should be able to execute MySQL queries for the selected database.

Create Database Tables And Add Data

Let’s run the following queries to create database tables.

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fname` varchar(32) NOT NULL,
  `lname` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
);

Create projects table and add the user_id foreign key.

CREATE TABLE `projects` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `project_user` (`user_id`),
  CONSTRAINT `project_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
);

Now insert some data.

INSERT INTO `users` VALUES (1,'John','Doe');
INSERT INTO `projects` VALUES (1,1,'Hello World');

Truncate The MySQL Table

Now run the following command to truncate the users table.

TRUNCATE users;

As we expected it will fail due to the foreign key constraint showing the following error message.

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`testdb`.`projects`, CONSTRAINT `project_user`)

To override this behaviour, set the FOREIGN_KEY_CHECKS to 0. So it will disable foreign key checks for the query. Add the following line before running the truncate command.

SET FOREIGN_KEY_CHECKS = 0;

Now again if you tried to truncate the users table, it will execute the query without any error.

TRUNCATE users;

Altogether, you can even run the following SQL query at once to get the same result.

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE users;
SET FOREIGN_KEY_CHECKS = 1;