SQL DROP TABLE
The SQL DROP TABLE statement is used to delete a table object and its data and related objects(primary key, foreign key, index, trigger and permission) .
This command needs to be executed carefuly as all the data will be lost within the deleted table and where it has been used as lookup column
Syntax
The basic syntax of this DROP TABLE statement is as follows −
DROP TABLE databasename ;
Example1
Lets assume we have tables called Users and Tasks with the following definition and data
Users
Definition
+---------+---------------+------+-----+
| Field | Type | Null | Key |
+---------+---------------+------+-----+
| ID | int | NO | PRI |
| NAME | varchar(50) | NO | |
+---------+---------------+------+-----+
Data
+---------+---------------+
| ID | NAME |
+---------+---------------+
| 1 | Thomas |
| 2 | Martin |
+---------+---------------+
Executing "DROP Table Users" statement will delete Users table definition and its related data and objects
Example2
Lets assume we have tables called Users and Tasks with the following definition and data
Users
Definition
+---------+---------------+------+-----+
| Field | Type | Null | Key |
+---------+---------------+------+-----+
| ID | int | NO | PRI |
| NAME | varchar(50) | NO | |
+---------+---------------+------+-----+
Data
+---------+---------------+
| ID | NAME |
+---------+---------------+
| 1 | Thomas |
| 2 | Martin |
+---------+---------------+
Tasks
Definition
+---------+---------------+------+-----+
| Field | Type | Null | Key |
+---------+---------------+------+-----+
| ID | int | NO | PRI |
| UserID | int | NO | FK |
| NAME | varchar(50) | NO | |
+---------+---------------+------+-----+
Data
+---------+---------------+---------------+
| ID | UserID | NAME |
+---------+---------------+---------------+
| 1 | 1 | Task1 |
| 2 | 1 | Task2 |
| 1 | 2 | Task3 |
+---------+---------------+---------------+
Executing "DROP Table Users" statement will fail as there is a foreign key to Tasks table on the UserID column