Wednesday , November 13 2019
Home / SQL / DROP TABLE statement and DROP TABLE with CASCADE CONSTRAINTS

DROP TABLE statement and DROP TABLE with CASCADE CONSTRAINTS

DROP TABLE statement:

The DROP TABLE statement is used to remove a table from oracle database.

Syntax:

DROP   TABLE   schema_name. table_name;

Or

DROP   TABLE   table_name;

Here,

DROP TABLE     will drop or remove oracle table.

table_name       is the name of the table that will be dropped or removed.

schema_name  is the name of user schema that belongs to the table.

Example:

DROP TABLE  HR.EMPLOYEE;

In the example, HR is the name of schema and EMPLOYEE is the name of the table. So, the EMPLOYEE table of HR schema will be dropped.

If you do not specify schema name then the table of the current user’s schema which user is currently logged in will be dropped.

Example:

 DROP TABLE  EMPLOYEE;

Note:  When you drop the table, the data of the table will be also deleted.

Restriction:

You cannot drop parent table keeping child table. So, to drop parent table you have to first drop child table and then parent table can be dropped. If you want to drop parent table without dropping child table first, oracle will generate an error and will not drop the table.

Suppose, you have created two tables, one is parent table named PRODUCT and another is child table named STORE. If you want to drop the parent table PRODUCT keeping the child table STORE, oracle will generate the following error:

DROP    TABLE   Product

Error at line 1

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

In this circumstance, to drop the parent table PRODUCT, first drop the child table STORE and then drop the parent table PRODUCT.

DROP TABLE statement with CASCADE CONSTRAINTS:

The DROP TABLE statement with CASCADE CONSTRAINTS is used to remove table along with all referential integrity constraints that refer to the PRIMARY KEY constraint of the dropped table.

When you want to drop parent table without dropping child table, you can use CASCADE CONSTRAINTS clause with DROP TABLE statement.

Syntax:

DROP TABLE  schema_name. table_name   CASCADE CONSTRAINTS;

Or

DROP TABLE   table_name   CASCADE CONSTRAINTS;

Here,

table_name       is the name of the table that will be dropped.

schema_name  is the name of schema that belongs to the table.

Example:

To complete the practice, the following steps should be executed.

Step-1:  Create the PRODUCT table as a parent table and insert rows into the table and commit.

CREATE TABLE Product

(

Product_Id           number(5)    PRIMARY   KEY,

Product_Name    varchar2(50),

Product_Type      varchar2(50)

);

The PRODUCT table has been created as a parent table by defining PRIMARY KEY in  Product_Id  column.

Insert 3 rows.

INSERT   INTO   Product   VALUES  (101,’Napa Extra’,’TAB’);

INSERT   INTO   Product   VALUES  (102,’Omep’,’CAP’);

INSERT   INTO   Product   VALUES  (103,’Seclo’,’CAP’);

Run COMMIT statement to save changes permanently.

COMMIT;

Now query the table to confirm whether data have been inserted.

SELECT * FROM Product;

Step-2:  Create the STORE  table as a child table and insert rows into the table and commit.

CREATE TABLE Store

(

Prd_Id           number(5)   REFERENCES   Product (Product_Id),

Quantity        number(10),

Price               number(10,2)

);

The STORE table has been created as a child table by defining referential integrity in Prd_Id column referencing the Product_Id  column of the parent  table PRODUCT.

Insert 2 rows.

INSERT   INTO  Store  VALUES  (101,10,3);

INSERT   INTO  Store  VALUES  (103,20,5);

Commit now to save changes permanently.

COMMIT;

Now query the table data.

SELECT * FROM Store;

Step-3: In the step, try to drop parent table PRODUCT without dropping the child table STORE.

DROP TABLE Product;

When the above statement will be executed to drop the PRODUCT table without dropping the   child table STORE, then oracle will generate the following message.

DROP TABLE Product

Error at line 1

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

Step-4: In the circumstance, execute DROP TABLE statement along with CASCADE CONSTRAINTS clause.

DROP TABLE  Product  CASCADE CONSTRAINTS;

In result, the parent table PRODUCT will be dropped and also all referential integrity that referencing the Product_Id  column of parent table PRODUCT will be dropped. So, The PRODUCT table will be removed without removing the child table STORE.

This is how, we can drop parent table keeping child table.

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

2 comments

  1. Hiya very cool web site!! Man .. Excellent .. Superb .. I will bookmark your website and take the feeds additionally…I’m glad to search out numerous helpful information here within the submit, we want work out extra techniques in this regard, thank you for sharing.

  2. It’s actually a cool and helpful piece of information. I’m glad that you shared this useful information with us. Please stay us up to date like this. Thanks for sharing.

Leave a Reply

Your email address will not be published. Required fields are marked *