Table of Contents
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
Read More