Using Drop a Foreign Key in Oracle

This Oracle tutorial explains how to drop a overseas key in Oracle with syntax and examples.

Description

Once a overseas key has been created, you may additionally locate that you wish to drop the foreign key from the table. You can do this with the ALTER TABLE statement in Oracle.

Syntax

The syntax to drop a overseas key in Oracle/PLSQL is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

If you had created a foreign key as follows:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);

In this example, we’ve got created a important key on the provider table referred to as supplier_pk. It consists of solely one area – the supplier_id field. Then we’ve created a overseas key called fk_supplier on the products table that references the supplier desk primarily based on the supplier_id field.

If we then wanted to drop the overseas key referred to as fk_supplier, we could execute the following command:

ALTER TABLE products
DROP CONSTRAINT fk_supplier;