How to delete a table in MySQL or MariaDB

Removing an obsolete table keeps an active MySQL or MariaDB schema easier to maintain, reduces backup and replication churn, and lowers the chance that applications or migration scripts will keep touching data structures that should no longer exist.

In current MySQL and MariaDB releases, DROP TABLE removes the table definition, rows, indexes, and table triggers in one DDL change. The statement acts on the current database unless the table name is fully qualified, and current vendor documentation still treats it as an implicit-commit operation for normal tables.

The rollback path is a restore, not a transaction undo, so the safest workflow is to confirm the exact database and table name, check for foreign-key dependencies, and take a dump before running the drop. Current validation also shows a naming split between the products: MySQL still provides mysql and mysqldump, while current MariaDB installs commonly provide mariadb and mariadb-dump instead.

1. Confirm the connected host, server version, and exact table name before deleting anything.

   $ mysql --host=db.example.net --port=3306 --user=dbadmin --password --table --execute "SELECT @@hostname AS host, @@port AS port, @@version AS version; SHOW TABLES FROM table_demo LIKE 'drop_demo';"
   Enter password:
   +--------------+------+---------+
   | host         | port | version |
   +--------------+------+---------+
   | db-01        | 3306 | 8.4.8   |
   +--------------+------+---------+
   +----------------------------------+
   | Tables_in_table_demo (drop_demo) |
   +----------------------------------+
   | drop_demo                        |
   +----------------------------------+

   Stop if the query does not show exactly one matching table. On shared servers, the most common destructive mistake is targeting the wrong schema or a similarly named table.

   On current MariaDB systems, replace mysql with mariadb when the MySQL compatibility client is not installed.

2. Create a logical backup of the table before dropping it.

   $ mysqldump --host=db.example.net --port=3306 --user=dbadmin --password --single-transaction --quick table_demo drop_demo > ./drop_demo-before-drop.sql
   Enter password:
   $ ls -lh ./drop_demo-before-drop.sql
   -rw-r--r-- 1 dbadmin dbadmin 2.1K Apr 10 09:18 ./drop_demo-before-drop.sql

   Confirm the dump file exists and is non-empty before continuing. The table drop removes both the structure and the data.

   On current MariaDB systems, use mariadb-dump instead of mysqldump. MariaDB documents that the old mysqldump name is deprecated and removed from the official Docker image from 11.0 onward.

3. Open an interactive SQL client connected to the target database.

   $ mysql --host=db.example.net --port=3306 --user=dbadmin --password table_demo
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   mysql>

   Keeping the database name on the command line avoids a later USE against the wrong schema. Use mariadb here when that is the client your host provides.

4. Confirm the table is still present and check whether any foreign keys still point at it.

   mysql> SHOW TABLES LIKE 'drop_demo';
   +----------------------------------+
   | Tables_in_table_demo (drop_demo) |
   +----------------------------------+
   | drop_demo                        |
   +----------------------------------+
   1 ROW IN SET (0.00 sec)
    
   mysql> SELECT TABLE_NAME, constraint_name
       -> FROM information_schema.KEY_COLUMN_USAGE
       -> WHERE referenced_table_schema = DATABASE()
       ->   AND referenced_table_name = 'drop_demo';
   Empty SET (0.00 sec)

   An empty result from the dependency query means nothing in the current database still references the target table through a foreign key.

   If this query returns rows, stop and remove the dependent foreign key or child table first. Current validation returned ERROR 3730 on MySQL 8.4 and ERROR 1451 on MariaDB 11.4 when a child table still referenced the parent.

5. Drop the table with IF EXISTS so a missing table becomes a warning instead of a hard failure.

   mysql> DROP TABLE IF EXISTS drop_demo;
   Query OK, 0 ROWS affected (0.02 sec)

   DROP TABLE implicitly commits the current transaction on normal tables, so it is not a change that ROLLBACK can undo later.

   Use a fully qualified name such as DROP TABLE IF EXISTS table_demo.drop_demo; when the command should not depend on the current database. Wrap identifiers in backticks when they contain reserved words or special characters, for example DROP TABLE IF EXISTS `order`;.

6. Verify that the table has disappeared from the database catalog.

   mysql> SHOW TABLES LIKE 'drop_demo';
   Empty SET (0.00 sec)

   If the statement waits instead of returning, another session may still hold a metadata lock on the table. Let the blocking transaction finish, or investigate the waiting session before retrying the drop.

7. Exit the client session.

   mysql> EXIT;
   Bye