DROP TABLE
Syntax
DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]
Description
DROP TABLE
removes one or more tables. You must have the DROP
privilege
for each table. All table data and the table definition are removed, as well as triggers associated to the table, so be
careful with this statement! If any of the tables named in the argument list do
not exist, MariaDB returns an error indicating by name which non-existing tables
it was unable to drop, but it also drops all of the tables in the list that do
exist.
Important: When a table is dropped, user privileges on the table are not automatically dropped. See GRANT.
If another connection is using the table, a metadata lock is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.
Note that for a partitioned table, DROP TABLE
permanently removes the table
definition, all of its partitions, and all of the data which was stored in
those partitions. It also removes the partitioning definition (.par) file
associated with the dropped table.
For each referenced table, DROP TABLE
drops a temporary table with that name, if it exists. If it does not exist, and the TEMPORARY
keyword is not used, it drops a non-temporary table with the same name, if it exists. The TEMPORARY
keyword ensures that a non-temporary table will not accidentally be dropped.
Use IF EXISTS
to prevent an error from occurring for tables that do not
exist. A NOTE
is generated for each non-existent table when using
IF EXISTS
. See SHOW WARNINGS.
If a foreign key references this table, the table cannot be dropped. In this case, it is necessary to drop the foreign key first.
RESTRICT
and CASCADE
are allowed to make porting from other database systems easier. In MariaDB, they do nothing.
Since MariaDB 5.5.27, the comment before the tablenames (that /*COMMENT TO SAVE*/
) is stored in the binary log. That feature can be used by replication tools to send their internal messages.
It is possible to specify table names as db_name
.tab_name
. This is useful to delete tables from multiple databases with one statement. See Identifier Qualifiers for details.
The DROP privilege is required to use DROP TABLE
on non-temporary tables. For temporary tables, no privilege is required, because such tables are only visible for the current session.
Note: DROP TABLE
automatically commits the current active transaction,
unless you use the TEMPORARY
keyword.
Examples
DROP TABLE Employees, Customers;