Diff. Between Drop, Delete and Truncate

Here in this post, we will learn the differences between Drop, Delete and Truncate in SQL.

We will also see some practical examples using SQL Queries -

·        DROP –

o   DROP is a DDL Statement.

o   Removes one or more Tables from the Database along with the Database Structure.

o   We cannot drop a Table which is Referenced by a Foreign Key Constraint.

o   The Transaction cannot be Rolled back, if we Drop a Table.

·        DELETE –

o   DELETE is a DML Statement.

o   Delete all or some of the Rows of the Table, also can be clubbed with ‘where’ condition.

o   Delete involves ‘one by one’ records and thus records entry in Transaction Logs, and thus ‘Rollback’ is possible.

o   It does not change any property of the Table.

o   It is much slower than Truncate.

o   Number of Deleted Rows are returned.

·        TRUNCATE –

o   Truncate is a DDL Statement.

o   It Deletes all the Rows at a time and cannot be Roll back.

o   It is much Faster than Delete statements.

o   ‘where’ condition is not applicable in Truncate statement.

o   Number of Deleted Rows are not returned.


We will discuss on the examples below  - 

Lets take the example of emp_test and dept_test. Dept_id in dept_test is a foreign_key constraint to dept_id of emp_test table -

Table -  emp_test  has the following 
      Primary Key -  employee_id, Foreign Key - Department_id
Table - dept_test has the following
      Primary Key - department_id


Lets try to delete emp_test. See what happens -

SQL > delete emp_test;

emp_test is now empty -

Now we will rollback and see, if we can get the values back and we are getting back.

Lets see, when we delete using some condition 

Now lets try delete from dept_test using some condition. Remember we defined some foreign key constraint on department_id. Lets see -

I could not delete the row because, I tried to violate a foreign key constraint. However, we can solve this Error using 'On Delete Cascade' key word. We first need to add or ALTER the constraint and add the keyword 'On Delete Cascade'. Now, if we try to delete, it will also delete the connected rows, eliminating the constraint.


Now, lets try TRUNCATING the table emp_test. Lets see what happens -

Lets try to Roll it back and see if we can get the Data back.

So, here, nothings coming back. its an implicit commit, DDL Statement.


Now lets try DROP Statement. 

The entire table structure is gone.












Now lets try DROP dept_test. We will get a Referential Integrity constraint error, as we have a defined Foreign Key Constraint and connected to emp_test. Lets see -


Thats all for now  - Thank You !!