Database Tutorial

Course Tutorial Site

Site Admin

DROP Statement

with 4 comments

This page shows you how to use the DROP statement. The DROP statement is a DDL statement. It lets you remove tables, sequence, views, object, and such from the database.

DROP statement
OracleMySQLSQL Server

Oracle

You can use the DROP statement to remove the definition of objects, tables, views, sequences, types, functions, procedures, and sequences. These examples demonstrate how to drop tables, sequences, and views.

DROP TABLE Statement

The following demonstrates removing the table structure and naturally any data from an Oracle database. It only works when there aren’t any FOREIGN KEY dependents in the database.

DROP TABLE military_user;

When you want to drop a table notwithstanding dependent tables, you append the CASCADE CONSTRAINTS clause. This is shown in the following example:

DROP TABLE military_user CASCADE CONSTRAINTS;

You can also drop a series of tables with a single PL/SQL anonymous block program unit. Inside the program unit, you call the DROP statement as many times as necessary. This is typically done in a loop. This is also how you drop tables contingent on whether or not they exist. The following shows you how to call the DROP TABLE statement inside a loop structure.

1
2
3
4
5
6
7
8
9
10
11
12
13

BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN ('MILITARY_USER','MILITARY_LOOKUP')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('MILITARY_USER_S1','MILITARY_LOOKUP_S1')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/

Oracle does not support rollback activity when you drop a table. All DDL commands, like the DROP statement are considered final. They don’t log data being removed like the DELETE command. When you drop a table, the data is lost unless it is also stored on a backup. Oracle does supports Flashback technology, which changes the DROP model because can enable recovery of the structure and data.

MySQL

You can use the DROP statement to remove the definition of objects, tables, views, sequences, types, functions, procedures, and sequences. These examples demonstrate how to drop tables, sequences, and views.

DROP TABLE Statement

The following demonstrates how you attempt to remove a table structure and naturally any data from a MySQL database. The only problem is that you might see an error when the table doesn’t already exist in your database.

DROP TABLE military_user;

You can fix that potential error by including the IF EXISTS clause. The IF EXISTS clause checks the database catalog to see if the table is there before attempting to drop it. You use it like this:

DROP TABLE IF EXISTS military_user;

You can also drop a series of comma-delimited tables with a single DROP statement. It’s important to note this syntax doesn’t support the IF EXISTS subclause.

The following shows you how to use the DROP TABLE statement.

DROP TABLE military_user, military_lookup;

SQL Server

Written by michaelmclaughlin

September 22nd, 2009 at 11:32 pm

Posted in

4 Responses to 'DROP Statement'

Subscribe to comments with RSS or TrackBack to 'DROP Statement'.

  1. Is there a way to do a conditional drop on a foreign key?

    Blaine Forbush

    15 Jan 10 at 10:44 pm

  2. Assuming that you’re asking about MySQL, there isn’t a single statement available for dropping a foreign key because none exists in the context of an ALTER statement.

    michaelmclaughlin

    16 Jan 10 at 11:18 am

  3. Paragraph 1 sentence 2 in the Oracle example says “These examples demonstrate how to define tables, sequences, and views.” Don’t the examples show how to drop tables, sequences, and views rather than define?

    Ben Davidson

    27 Apr 10 at 7:33 pm

  4. Great catch. I’ve fixed both instances of the error. Thanks!

    michaelmclaughlin

    21 Jun 10 at 10:28 pm

Leave a Reply