Oracle/PLSQL: Drop a foreign key

The syntax for dropping a foreign key is:

ALTER TABLE table_name
drop CONSTRAINT constraint_name;

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