Using Drop a Foreign Key in Oracle

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


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.


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

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;


If you had created a foreign key as follows:

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

( 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;