MySQL — Truncate a Foreign Key Constrained Table

We’re working on the next iteration of the Future Studio website. The new website uses MySQL as the database. The database schema uses foreign key relationships between columns in different tables. At some point, we ran into the following issue when truncating the data in a MySQL table:

[MySQL] Cannot truncate a table referenced in a foreign key constraint.


This tutorial shows you how to truncate data in a MySQL table containing foreign keys.

MySQL Series Overview

How to Truncate a Foreign Key Constrained Table in MySQL or MariaDB

You can’t truncate data from a MySQL table that contains a foreign key. You’ll always receive the “cannot truncate a table referenced in a foreign key constraint” error from above.

You can force MySQL to allow truncating data from a foreign key constraint table: you’re deactivating foreign key checks for the truncate operations.

Telling MySQL to not check foreign key restrictions allows you to truncate tables, even if they contain foreign keys:


truncate users;  
truncate tutorials;  
truncate subscriptions;


Enjoy truncating data from MySQL tables!

Explore the Library

Find interesting tutorials and solutions for your problems.