Oracle DROP TABLE

Oracle DROP table statement is used to delete a table from the database.

Syntax:

DROP TABLE schema_name.table_name
[CASCADE CONSTRAINTS | PURGE];

Here:

• schema_name – It is the name of schema in which the table belongs. If we don’t give the schema name, Oracle will assume the schema to be the same in which user belongs.
• CASCADE CONSTRAINTS – If there are referential integrity constraints present in the table which refer to primary and unique key constraints in other table, we need to specify CASCADE CONSTRAINTS clause to remove all such constraints. If such constraints exist and we fail to use this clause, Oracle is bound to return an error preventing the deletion of table.
• PURGE – We need to specify this clause in case we want to permanently delete the table and free up the space taken by it. If we don’t mention this clause, then Oracle will first put the table into recycle bin here the table and its related object will continue to take up space in Oracle database.
  Note: One must be careful while using PURGE clause, because oracle does not provide the option to roll back or recover the table once its dropped.

Let’s have a look at some of the examples to understand this better.

Oracle DROP Table Examples

1. Oracle DROP table example

Let’s create a sample table named interns that we will be using for this example:

CREATE TABLE interns (
    intern_id NUMBER,
    first_name VARCHAR2(50) NOT NULL,
    salary NUMBER(10) NOT NULL,
    job_role VARCHAR(10) NOT NULL,
    PRIMARY KEY(person_id)
);  

We use the following query to drop the table:

DROP TABLE interns;

2. Oracle DROP TABLE CASCADE CONSTRAINTS example

Let’s create two tables which have referential integrity constraints for this example:

CREATE TABLE teachers(
    teacher_id NUMBER PRIMARY KEY,
    teacher_name VARCHAR2(20)
    subject_name VARCHAR2(20)
);

CREATE TABLE students(
    student_id NUMBER PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    enrollment_date DATE NOT NULL,
    subject_name VARCHAR(30) NOT NULL,
    teacher_id NUMBER NOT NULL,

    CONSTRAINT fk_teacher 
    FOREIGN KEY (teacher_id) 
    REFERENCES teachers(teacher_id) ON DELETE CASCADE
);

As we can see, a teacher has multiple students linked to it by teacher_id in students table.

Let’s use the following query for dropping the teachers table.

DROP TABLE teachers;

Oracle returns an error:

ORA-02449: unique/primary keys in table referenced by foreign keys

This happens because the primary key of teachers table, i.e, teachers_id is currently referenced by teacher_id column in students table.

We will be using the CASCADE CONSTRAINTS clause for deleting the table which has referential integrity constraints.

DROP TABLE teachers CASCADE CONSTRAINTS;

This query not only dropped the teachers table but also the foreign key constraint fk_teacher from the students table.

3. Oracle DROP TABLE PURGE example

We use the PURGE clause with DROP TABLE to permanently delete a table, let’s have a look at example:

DROP TABLE students purge;

In this article, we learned how to use DROP TABLE statement to drop a table from the database.