6.5.6 DROP TABLE Syntax

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]

DROP TABLE removes one or more tables. All table data and the table definition are removed, so be careful with this command!

In MySQL Version 3.22 or later, you can use the keywords IF EXISTS to prevent an error from occurring for tables that don't exist. In 4.1 one gets a NOTE for all not existing tables when using IF EXISTS. See section 4.5.7.9 SHOW WARNINGS | ERRORS.

RESTRICT and CASCADE are allowed to make porting easier. For the moment they don't do anything.

Note: DROP TABLE will automatically commit current active transaction (except if you are using 4.1 and the TEMPORARY key word.

Option TEMPORARY is ignored in 4.0. In 4.1 this option works as follows:

• Only drops temporary tables.
• It doesn't end a running transactions.
• No access rights is checked.

Using TEMPORARY is a good way to ensure that you don't accidently drop a real table.

User Comments

It would be nice if LIKE syntax could be used in a
DROP statement.

DROP TABLE DIMENSIONEN CASCADE
CONSTRAINTS; does not work when the table
DIMENSIONEN does not exist, this should be
changed... the full execution is

DROP TABLE DIMENSIONEN CASCADE
CONSTRAINTS;

CREATE TABLE DIMENSIONEN (
DIMENSION_IND NUMBER(2) NOT NULL,
DIMENSION VARCHAR2(8) NULL,
DIMENSION_TEXT VARCHAR2(30) NULL
);


ALTER TABLE DIMENSIONEN
ADD ( PRIMARY KEY (DIMENSION_IND) ) ;

If a table created with this option "data directory=.. index directory = .. "
MySQL creates symbolic links for the table.
But "drop table" only deletes links, so can't create same name table which has same data and index directory without deleteing data and index files manually.

How would you drop a table, if the table name has a - in the table name.

i.e.
mysql> DROP TABLE bandwidth-t1;
returns: ERROR 1064: You have an error in your SQL syntax near '-t1' at line 1

mysql> show tables;
+----------------+
| Tables_in_apps |
+----------------+
| bandwdith-t1 |
| customer |
+----------------+

Eric,
Although, we were not supposed to ask questions, I'll try to answer yours here.

I would think that if you have a database with a dash inside, try putting the database name in single quotes...

DROP TABLE 'My-Table';

...and see what happens. I have not tried this, but I think it should work.

It would be useful if 'jolly charachters' (such as *) would be allowed in DROP TABLE, so it would be possible to drop tables whose name matches a pattern.

Add your own comment.