08 - SQL DROP

As part of DDL, SQL DROP is used to delete the existing databases and tables from the SQL schema.

a. DROP Database

This command will delete the entire database.

Syntax of DROP database is as follows:

DROP DATABASE database_name;

Example: 

Check the number of databases in the system.

+--------------------------------------+
| Database                       |
+--------------------------------------+
| information_schema             |
| creditdb                       |
| employeedb                     |
| moviesapp                      |
| mysql                          |
| performance_schema             |
| sakila                         |
| test                           |
| world                          |
+--------------------------------------+

9 rows in set (0.00 sec)

Delete an existing database test, use below statement:

 

Mysql>DROP DATABASE test;

 Once executed, you can check if the database has been deleted using the command SHOW Databases;

+--------------------------------------+
| Database                      |
+--------------------------------------+
| information_schema            |
| creditdb                      |
| employeedb                    |
| moviesapp                     |
| mysql                         |
| performance_schema            |
| sakila                        |
| world                         |
+--------------------------------------+

8 rows in set (0.00 sec)

Please note, that before deleting the database, you are sure that you do not need the data from the databases or have taken necessary backups. Also you need to have administrator rights for deleting databases. The database name should be unique in the database management system.

DROP Table

DROP Table statement is used to delete the table from the database, it means all the definition, constraints, permissions and data stored in tables will be deleted. Hence you have to careful while using the DROP statement. The table_name should  be unique.

Syntax:

DROP TABLE table_name;

Example:

Let us first check how many tables are there in the database using SHOW statement.

+-----------------------------+
| Tables_in_test           |
+-----------------------------+
| departments              |
| employee                 |
+-----------------------------+

2 rows in set (0.00 sec)


Now delete table departments.  You cannot delete departments table until you have deleted or cleared all the constraints binding the field in department table. Like here the DEPT_ID is linked to employee table as a foreign key hence will not allow to delete the table. See below screenshot.

mysql> DROP TABLE DEPARTMENTS;

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fail

Hence first we will have to delete Employee table and the departments table.

 

mysql> DROP TABLE EMPLOYEE;

Query OK, 0 rows affected (0.05 sec)

 

mysql> DROP TABLE DEPARTMENTS;

Query OK, 0 rows affected (0.05 sec) 

Now check the database using SHOW statement: 

 

mysql> SHOW TABLES;

Empty set (0.00 sec)

Like us on Facebook