In MySQL , as in other DBMS, you can clear tables. Clearing a table allows you to delete data without affecting the very structure of the table. There are several ways to clean up a table in MySQL. In particular, you can highlight table cleanup using the DELETE and TRUNCATE commands.
Both commands perform the same task, but have a few differences, which will be discussed later in the article. It will also be mentioned about deleting data from a table in the presence of foreign keys ( foreign key ). This article will discuss how to clear a table in MySQL in various ways in the Ubuntu 20.04 operating system.
How To Clear Table in MySQL
There are several ways to clean up a table in MySQL. All possible methods will be discussed below.
1. Deleting Table Data With Delete
To delete data from a table, you can use the DELETE statement , which can delete table rows according to a given condition. Suppose there is a MyGuests table that has a user named John :
Using the DELETE statement and the specified condition – in this case, the deletion will occur on the firstname column, which takes the value of the username, the record with the number (id) 1 and the name John will be deleted:
$ DELETE FROM MyGuests WHERE firstname = 'John';
Also, the DELETE statement can delete all rows of a table at once. As an example, there is a table with two records:
Let’s delete all rows at once without setting any conditions. To do this, you need to execute the following SQL query:
$ DELETE FROM MyGuests;
2. Deleting Table Data With Truncate
There is also a special TRUNCATE command to delete all rows in a table. It is similar to DELETE; however, it does not allow the use of WHERE. It is also worth highlighting the following features when clearing a MySQL table using the TRUNCATE statement:
- TRUNCATE does not allow you to delete individual lines;
- DELETE locks every row, and TRUNCATE the entire table;
- TRUNCATE cannot be used with tables containing foreign keys of other tables;
- After using the TRUNCATE statement , no information about the number of deleted rows from the table is displayed in the console.
As an example, let’s take a table with two records from the previous example:
To clear this MySQL table of all records, execute the following SQL query:
$ TRUNCATE MyGuests;
As mentioned earlier, the TRUNCATE command does not display the number of deleted rows in the table, so the text 0 rows affected was printed to the console.
How to Clear a Table With Foreign Key Constraint
If the table contains foreign keys ( Foreign Key ), then just clearing the table will not work. Suppose there are two tables – Equipment and EquipmentCategory :
The Equipment table has a column named category_id that is foreign-keyed to the id column in another table, EquipmentCategory . For example:
Equipment:
- id
- category_id
- name
EquipmentCategory:
- id
- name
If you try to clear all rows of the EquipmentCategory table using the TRUNCATE statement, you will get the following error:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`Inventory`.`Equipment`, CONSTRAINT `Equipment_ibfk_1`)
This error indicates that the table refers to another table and has a deletion constraint in the form of a foreign key. To avoid this error, you can use disabling foreign key checking and adding the ON DELETE CASCADE parameter when creating a table.
This error indicates that the table refers to another table and has a deletion constraint in the form of a foreign key. To avoid this error, you can disable foreign key checking and add the ON DELETE CASCADE parameter when creating a table.
1. Disable Foreign Key Checking
To clear the table containing foreign keys, you can disable foreign key checking. To do this, run the following command:
SET FOREIGN_KEY_CHECKS=0;
This command will disable foreign key checking and thus allow you to clear the table using the TRUNCATE command :
$ TRUNCATE Equipment;
You can also clear a table with DELETE :
$ DELETE FROM Equipment;
After clearing the table, you can return the foreign key check with the command:
$ SET FOREIGN_KEY_CHECKS=1;
However, this method is not recommended because you lose data consistency in the database, and the program using it may not work correctly. There is another solution. When dropping a table, delete all related records in other tables.
2. Adding the on Delete Cascade Option
The ON DELETE CASCADE option implicitly deletes rows from a child table whenever rows are deleted from the parent table. The ON DELETE CASCADE option can be set when creating the table, but if you haven’t done so, you can delete the CONSTRAINT and re-create it.
To view information about a foreign key contained in a table, execute the SHOW CREATE TABLE command:
$ SHOW CREATE TABLE EquipmentCategory;
In this example, the table is called EquipmentCategory and it has a constant called EquipmentCategory_ibfk_1.
Next, you need to drop the foreign key using the ALTER TABLE and DROP commands . For example, if EquipmentCategory is the table name and EquipmentCategory_ibfk_1 is the foreign key name, run:
$ ALTER TABLE EquipmentCategory DROP FOREIGN KEY EquipmentCategory_ibfk_1;
After that, the foreign key must be returned back by adding the ON DELETE CASCADE option to it. The command will be as follows :
$ ALTER TABLE EquipmentCategory ADD FOREIGN KEY (category_id) references EquipmentCategory(id) on DELETE CASCADE;
The table can then be cleared with the TRUNCATE command :
$ TRUNCATE EquipmentCategory;
Conclusion
This article looked at how to clear a MySQL table from data. The DELETE and TRUNCATE commands were reviewed, and tables filled with information were created to demonstrate the deletion of information.
