FOREIGN KEY with ON DELETE SET NULL 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 ON DELETE SET NULL in Oracle SQL / PLSQL:

  1. A FOREIGN KEY 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 with cascade delete can be defined in CREATE TABLE or ALTER TABLE statement.

A FOREIGN KEY with ON DELETE SET NULL means that whenever a record from the parent table is deleted all the corresponding record(s) in the child table will have the FOREIGN KEY fields set to NULL. The records from the child table are not deleted unlike ON DELETE CASCADE.

Syntax to create a FOREIGN KEY with ON DELETE SET NULL in CREATE statement is:

CREATE TABLE table_name
(column_name1 datatype null/NOT NULL
,column_name2 datatype null/NOT NULL
,column_name3 datatype null/NOT NULL
.
.
,column_nameN datatype null/NOT NULL
,CONSTRAINT constraint_name
FOREIGN KEY (column_name1, column_name2, . . . column_nameN )
REFERENCES parent_table_name(column_name1, column_name2, . . . column_nameN)
ON DELETE SET NULL
);

Syntax to create a FOREIGN KEY with ON DELETE SET NULL in ALTER statement is:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1,column_name2, . . column_nameN)
REFERENCES parent_table_name (column_name1,column_name2, . . column_nameN)
ON DELETE SET NULL;

Let’s take an example for understanding:

Scenario 1:

Step 1: 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

Employee_Id Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support
105 Emp E 32000 Sales

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

Emp_Id Commission_Percent
102 20
103 20
104
105 10

We can achieve the same as:
[sourcecode language=”sql”]

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)
);
[/sourcecode]

[sourcecode language=”sql”]

CREATE TABLE comm
(emp_id NUMBER(10)
,commission_percent NUMBER(20)
,CONSTRAINT fk_employee
FOREIGN KEY (emp_id)
REFERENCES employee(employee_id)
ON DELETE SET NULL
);
[/sourcecode]

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.

Step 2: Inserting the data in the ‘employee’ table.

[sourcecode language=”sql”]

INSERT INTO employee
VALUES (101,’Emp A’,10000,’Sales’);

INSERT INTO employee
VALUES (102,’Emp B’,20000,’IT’);

INSERT INTO employee
VALUES (103,’Emp C’,28000,’IT’);

INSERT INTO employee
VALUES (104,’Emp D’,30000,’Support’);

INSERT INTO employee
VALUES (105,’Emp E’,32000,’Sales’);
[/sourcecode]

Step 3: Inserting the data in the ‘comm’ table.

[sourcecode language=”sql”]

INSERT INTO comm
VALUES (102,20);

INSERT INTO comm
VALUES (103,20);

INSERT INTO comm
VALUES (104,NULL);

INSERT INTO comm
VALUES (105,10);
[/sourcecode]

The above SQL INSERT statements will insert 5 rows in the ‘employee’ table and 4 rows in ‘comm’ table.

Now, if we query the employee table as:

[sourcecode language=”sql”]
SELECT *
FROM employee;
[/sourcecode]

We will get the following result:

Employee_Id Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support
105 Emp E 32000 Sales

Now, if we query the ‘comm’ table as:

[sourcecode language=”sql”]
SELECT *
FROM comm;
[/sourcecode]

We will get the following result:

Emp_Id Commission_Percent
102 20
103 20
104
105 10

Here we have successfully created ‘employee’ and ‘comm’ tables with ‘employee_id’ as PRIMARY KEY for ‘employee’ table and ‘emp_id’ as foreign key for ‘comm’ and also inserted data in ‘employee’ and ‘comm’ tables.

If we try to insert one more record in ‘comm’ table of ‘emp_id = 106’ as:

[sourcecode language=”sql”]
INSERT INTO comm
VALUES (106,30);
[/sourcecode]

We get an error that integrity constraint is getting violated and the data cannot be entered, this is because ‘emp_id = 106’ is not found in ‘employee’ table and hence the record cannot be entered in ‘comm’ table.

Lets delete a record of ‘employee_id =105’ from ‘employee’ table

[sourcecode language=”sql”]
DELETE FROM employee
WHERE employee_id = 105;
[/sourcecode]

The above statement will delete a record of ‘employee_id = 105’ from ‘employee’ table and when we query the ‘employee’ table as

[sourcecode language=”sql”]
SELECT *
FROM employee;
[/sourcecode]

We will get the below result

Employee_Id Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support

Also if we query the ‘comm’ table as

[sourcecode language=”sql”]

SELECT *
FROM comm;
[/sourcecode]

We get the below result:

Emp_Id Commission_Percent
102 20
103 20
104
10

We can see that the record for ‘emp_id = 105’ has been set to NULL in the ‘comm’ table because we have written ON DELETE SET NULL while creating FOREIGN KEY in ‘comm’ table.


Scenario 2:

Using ALTER TABLE statement to create a FOREIGN KEY with ON DELETE CASCADE

Syntax for creating a FOREIGN KEY using ALTER TABLE statement is:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1,column_name2, . . column_nameN)
REFERENCES parent_table_name (column_name1,column_name2, . . column_nameN)
ON DELETE SET NULL;

Example:

[sourcecode language=”sql”]

ALTER TABLE comm
ADD CONSTRAINT comm_fk
FOREIGN KEY (emp_id)
REFERENCES employee (employee_id)
ON DELETE SET NULL;
[/sourcecode]


Leave a Reply

Your email address will not be published. Required fields are marked *