Oracle / PLSQL: Drop a Foreign Key

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

Description

Once a foreign key has been created, you may find 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 foreign 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 created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.

If we then wanted to drop the foreign key called fk_supplier, we could execute the following command:

ALTER TABLE products
DROP CONSTRAINT fk_supplier;