Oracle Database

Oracle Drop Table If Exists

This tutorial aims to show you how to work with the DROP TABLE command in Oracle databases. This command removes an existing table from a given database either to the recycle bin or permanently.

Oracle Drop Table Statement

The following defines the syntax of the DROP TABLE command:

DROP TABLE schema_name.table_name

[CASCADE CONSTRAINTS | PURGE];

We start by calling the DROP TABLE command followed by the schema name in which the target table resides. Unlike other database engines, Oracle requires you to specify the target schema. Otherwise, Oracle will assume that you wish to remove the table from your schema, which might not be your intended operation.

Next is the table name that you wish to remove. Again, this must be an existing table in the specified schema. Finally, you must also have sufficient privileges on the target table to execute a drop operation.

The second part, CASCADE CONSTRAINTS, tells Oracle to remove all referential integrity constraints associated with the table’s primary and unique keys.

If you do not specify this clause and the table contains such referential constraints, Oracle returns an error and fails to remove the table.

The PURGE keyword tells Oracle to remove the table from the schema and release the allocated space. The PURGE option allows you to permanently remove the table instead of placing it in the recycle bin.

CAUTION: Be careful when using the PURGE option. It completely removes the ability to perform a rollover or table recovery after executing the drop command. Therefore, skip this option if you wish to recover the table for later use. However, it can be a helpful feature when you need to shred the sensitive databases from the recycle bin.

Oracle Drop Table Example Illustration

The following are examples of using the DROP TABLE statement in Oracle databases:

Example 1: Basic Oracle Drop Table Command

The following shows the simple create table statements that initialize a simple table for illustration purposes.

Feel free to skip this section if you have a target table that you wish to remove. Otherwise, proceed to the proceeding step.

CREATETABLE film (
    film_id      NUMBER
GENERATEDASIDENTITY,
    title        VARCHAR(100) NOTNULL,
    release_year INTNOTNULL,
    description  VARCHAR(255),
languageVARCHAR(50),
    rating       VARCHAR(50),
    imbd_score   FLOAT,
    rental_rate  FLOAT
);

Once we have the given sample table, we can drop it as shown in the following query:

DROP TABLE film;

In this case, since the table does not contain any constraints, we do not need to use the CASCADE CONSTRAINTS clause.

Example 2: Oracle Table Drop Table with Constraints

In this example, we wish to illustrate how to remove a table with primary and foreign key constraints.

CREATETABLE studio (
    studio_id  NUMBER PRIMARY KEY,
    studio_name VARCHAR2(50)
);

CREATETABLE film (
    film_id NUMBER PRIMARY KEY,
    title        VARCHAR(100) NOTNULL,
    release_year INTNOTNULL,
    description  VARCHAR(255),
languageVARCHAR(50),
    rating       VARCHAR(50),
    imbd_score   FLOAT,
    rental_rate  FLOAT,
    studio_id numbernotnull,
CONSTRAINT f_studio FOREIGNKEY(studio_id)
REFERENCES studio (studio_id)
ONDELETECASCADE
);

If we attempt to drop the table without removing the constraints, Orcalereturns an error as shown:

DROP TABLE studio;

Output:

[2022-11-14 04:28:33] [72000][2449] ORA-02449: unique/primary keys in table referenced by foreign keys

This is because the ID references the primary key of the studio table.

Therefore, to remove the studio table, we need to use the CASCADE CONSTRAINTS keyword as shown in the following example:

DROP TABLE studio CASCADE CONSTRAINTS ;

With that, you should successfully remove the table from the schema.

It is good to ensure that the referenced objects are not critical. In some cases, cascading constraints can lead to data failure on the tables and the entire database. Use it carefully.

Example 3: Oracle Delete Table (Purge Mode)

To remove a table without storing it in the recycle bin, we can use the PURGE keyword as illustrated in the following example:

DROP TABLE film purge;

This should allow you to permanently remove the table and release the occupied space back to the disk.

Example 4: Oracle Delete Multiple Tables

Unlike other well-thought-out databases such as PostgreSQL, MySQL, and SQL Server, Oracle does not provide a way of removing the multiple tables at once.

You can, however, use an SQL block to perform such an operation as follows:

BEGIN
FOR i IN
        (
SELECT tbl_name
FROM all_tables
WHERE table_name LIKE'pattern'
        )
LOOP
executeimmediate'drop table'||i.tbl_name || 'cascade constraints' || 'purge';
endloop;
END;

You can modify the previous script to remove the tables following the specified pattern.

Conclusion

In this post, we explored the fundamentals of working with the drop table command in oracle. This command allows you to remove an existing table from a given schema. You also learned how to delete a table to recycle bin or remove it permanently. We hope you found this article helpful!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list