DROPPING, DISABLING and ENABLING a FOREIGN KEY in Oracle SQL – PLSQL

In Oracle SQL / PLSQL a FOREIGN KEY is column / field that appears in one table and must appear in another table.

Important points about FOREIGN KEY in Oracle SQL / PLSQL:

1. A FOREIGN KEY in Oracle SQL / PLSQL creates a parent child pattern between two tables.
2. The referenced table is called the PARENT table and the table having the FOREIGN KEY is called as the CHILD table.
3. The FOREIGN KEY in child table generally references PRIMARY KEY in parent table.
4. A foreign key can be dropped, disabled or enabled in ALTER TABLE statement.

Scenario 1:

Dropping a FOREIGN KEY

Syntax to drop a FOREIGN KEY in ALTER statement is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Let’s take an example for understanding:
Suppose we want to create a table named ‘employee’ in the database as shown below, with ‘employee_id’ as the primary key for the ‘employee’ table

And we want to create a table named ‘comm’ with ‘emp_id’ as FOREIGN KEY as shown below:

We can achieve the same as:

CREATE TABLE employee
(employee_id   NUMBER(10)       NOT NULL
,employee_name VARCHAR2(500)    NOT NULL
,salary        NUMBER(20)       NOT NULL
,department    VARCHAR2(300)    NOT NULL
,CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);

CREATE TABLE comm
(emp_id             NUMBER(10)
,commission_percent NUMBER(20)
,CONSTRAINT fk_employee
 FOREIGN KEY (emp_id)
 REFERENCES employee(employee_id)
);

Here with the help of the above SQL CREATE statement we have created a table named ‘employee’ that has 4 columns namely ‘employee_id’, ‘employee_name’, ‘department’ and ‘salary’ and we are having the ‘employee_id’ column as the primary key for the ‘employee’ table.

Also, we have created a new table named ‘comm’ which has 2 columns namely ‘emp_id’ and ‘commission_percent’ and we are having ‘emp_id’ as foreign key referencing ‘employee_id’ of ‘employee’ table.

We want to drop the FOREIGN KEY constraint from ‘comm’ table then we can achieve it as:

ALTER TABLE comm
DROP CONSTRAINT fk_employee;

Scenario 2:

Disabling a FOREIGN KEY

Syntax for disabling a FOREIGN KEY using ALTER statement is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example:

ALTER TABLE comm
DISABLE CONSTRAINT fk_employee;

Scenario 3:

Enabling a FOREIGN KEY

Syntax for enabling a FOREIGN KEY using ALTER statement is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example:

ALTER TABLE comm
ENABLE CONSTRAINT fk_employee;