Delete table in SQL / DROP TABLE in SQL [Practical Examples]

How to delete table in SQL

SQL DROP TABLE command is used to remove a table structure and its all records with indexes, triggers, constraints and also all the permission on that table, to remove the stored procedures and the view references to the dropped table we need to used DROP VIEW and DROP PROCEDURE explicitly

 

SQL DROP TABLE Syntax

DROP TABLE [TEMPORARY] [IF EXISTS]
database_name.schema_name.table_name [,database_name.schema_name.table_name] ...
[RESTRICT | CASCADE]

Here,

database_name: Is the name of the database in which the table was created.

IF EXISTS: Conditionally drops the table only if it already exists.

schema_name: Is the name of the schema to which the table belongs.

table_name: Is the name of the table to be removed

[RESTRICT | CASCADE]: CASCADE has the effect of dropping all SQL objects that are dependent on that object. RESTRICT is the default for the drop behavior

RESTRICT looks to see what objects are dependent on the object being dropped. If there are dependent objects, then the dropping of the object does not occur

IF EXISTS: If we used DROP TABLE Statement with the table name which don’t exist than DROP TABLE command result is depend on whether we have used IF EXISTS with DROP TABLE Statement as follow

• WITHOUT IF EXISTS : the command execution will be failed and its shows error as tables does not exists or unable to drop table,
• WITH IF EXISTS: no error occurs if table does not exists,

TEMPORARY keyword: 

• DROP TABLE causes an implicit commit, except when used with the TEMPORARY keyword
• The TEMPORARY keyword has the following effects:
• The statement drops only TEMPORARY tables.
• The statement does not cause an implicit commit.
• No access rights are checked. A TEMPORARY table is visible only with the session that created it, so no check is necessary

 

Key points of DELETE TABLE/ DROP TABLE

• DROP TABLE cannot be used to drop a table that is referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first
• When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints
• If you delete all rows in a table by using DELETE tablename or use the TRUNCATE TABLE statement, the table exists until it is dropped

 

Examples of SQL DROP Table

Consider school result management database for performing practical examples of DELETE table in SQL

The above figure shows the primary – foreign key relationship between the tables of school result management database

 

Delete a table in the current database

Example 1 : Write SQL Query to remove the faculty table from the dbschool

DROP TABLE tblfaculty

• In the above query, we have used SQL DROP TABLE Statement to delete the faculty table from dbschool
• once we execute above query it will ask for the conformation to delete mentioned table from the database , as shown below

If we click on yes , the query will be executed successfully

OUTPUT:

To see the result of above DROP TABLE Statement, we can use SQL DESC command for MySQL or ORACLE and for SQL Server we can use sp_help or Select Statement to check whether the object is deleted or not

 

SQL SERVER

sp_help tblfaculty

 

ORACLE/ MySQL

 

Dropping a table in another database

We can delete a table of another database by specifying database name with the table name

Example 2 : Write SQL Query to delete employee table from company database

DROP TABLE dbcomapny.tblemp

In the above query, we have used drop table statement with database name dbcomapny and table name tblemp to remove employee table from dbcompany database

OUTPUT:

To see the result of above DROP TABLE Statement, we can use SQL DESC command for MySQL or ORACLE and for SQL Server we can use sp_help or Select Statement to check whether the object is deleted or not

 

SQL SERVER

sp_help  tblemp

 

ORACLE/ MySQL

 

Dropping a temporary table

The temporary tables are used to store data for an amount of time in SQL Server. Many features of the temporary tables are similar to the persisted tables. Such as, we can create indexes, statistics, and constraints for these tables like we do for persisted tables

The name of this type of temporary table starts with a single “#” hashtag symbol, and they are solely visible on the created session

Example 3 : Write SQL Query to create temporary table localemployee and delete the same table using DROP TABLE

CREATE TABLE #Localemployee
(
 EmpId int,
 EmpNameName varchar(50), 
 EmpAdress varchar(150)
)
drop table #Localemployee

• In the previous two queries , we have first create temporary table #Localemployee using create table statement
• when we execute the second query the DROP TABLE command will delete the  temporary created table

OUTPUT:

To see the result of above DROP TABLE Statement, we can use SQL DESC command for MySQL or ORACLE and for SQL Server we can use sp_help or Select Statement to check whether the object is deleted or not

 

SQL SERVER

sp_help  Localemployee

 

ORACLE/ MySQL

 

Dropping a table using IF EXISTS

If we are deleting non existed table or already deleted table, SQL returns error like ‘the object does not exist’ to overcome this error outcome, we can use IF EXIST optional argument of DROP TABLE Statement

Example 4: Write SQL query to delete student table if it is exist

DROP TABLE IF EXISTS tblstudent;

• In the previous query , DROP TABLE Statement is used to delete student table
• If exist argument is used to check for the table existence to deleted
• tblstudent is deleted as it is exist in dbschool database

OUTPUT:

To see the result of above DROP TABLE Statement, we can use SQL DESC command for MySQL or ORACLE and for SQL Server we can use sp_help or Select Statement to check whether the object is deleted or not

 

SQL SERVER

sp_help  tblstudent

 

ORACLE/ MySQL

 

Drop a table with a foreign key constraint example

SQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent

We must drop the constraint before deleting the table in SQL, Otherwise its rule violation that could break the databases Referential Integrity

 

Syntax to drop a FOREIGN KEY constraint in SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>;

MySQL:

ALTER TABLE <table_name>
DROP FOREIGN KEY <constraint_name>;

 

Example 5: Write SQL Query to delete table result of school database

alter table tblresult
drop constraint FK_tblresult_tblstudent;
drop table tblresult;

• In the above query, first we have drop the constraint FK_tblresult_tblstudent using alter table and drop constraint statement
•  In the second query we have drop the table result using DROP TABLE statement

OUTPUT:

To see the result of above DROP TABLE Statement, we can use SQL DESC command for MySQL or ORACLE and for SQL Server we can use sp_help or Select Statement to check whether the object is deleted or not

 

SQL SERVER

sp_help  tblresult

 

ORACLE/ MySQL

 

Summary

In this article of delete table in SQL, we have covered the delete table command DROP TABLE used to delete a table in SQL with syntax and explanation of each syntax arguments, practical examples of delete table query with output is also covered, practical examples of dropping table from current database, deleting table of another database, deleting temporary table, delete a table with if exist, delete a table having a foreign key constraint have been explained

 

References

SQL ALTER TABLE

 

Read More

SQL Drop Table