Referential Integrity Constraints in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Referential Integrity Constraints in Oracle with Examples

In this article, I am going to discuss Referential Integrity Constraints in Oracle with Examples. This is a continuation part of our previous article, so, please read our previous article where we discussed Foreign Key Constraint in Oracle before proceeding to this article.

What are Referential Integrity Constraints in Oracle?

The Referential Integrity Constraints in Oracle are nothing but the foreign key constraints that tell Oracle Database to perform certain actions whenever a user attempts to delete or update a primary key for which existing foreign keys point.

What are the Actions Performed by Oracle?

In order to tell what actions to perform whenever a user trying to delete or update a primary key value for which existing foreign key values point, Oracle provided the following Referential Integrity Constraints which we need to set while creating the foreign key constraints.

1. SET NULL: If a user tries to delete statement(s) that will affect rows in the foreign key table, then those values will be set to NULL when the primary key record is deleted in the Primary key table. The important thing that we need to keep in mind is that the foreign key columns affected must allow NULL values.
2. CASCADE: If a user tries to delete the statement(s) which will affect the rows in the foreign key table, then those rows will also be deleted when the primary key record is deleted.

Example to understand Referential Integrity Constraints in Oracle:

Let us understand the Referential Integrity Constraints in Oracle with examples. In order to understand this concept, first, we need two tables. So, let’s first create two tables (Department and Employees) and enforce the primary and foreign key constraints as follows.

Department Table (Primary Key Table):

The Department table is going to be our primary key table. Please execute the below SQL Script to create the Department table with the required data.

CREATE TABLE Department( 
    Id   INT PRIMARY KEY, 
    Name VARCHAR2(10) 
);

Insert into Department values (10, 'IT');
Insert into Department values (20, 'HR');
Insert into Department values (30, 'INFRA');

Employees Table (Foreign Key Table):

The Employees table is going to be our foreign key table. Please execute the below SQL Script to create the Employees table with the required data. In the Employees table, the DepartmentId column is the foreign key which is referencing the ID column of the Department table.

CREATE TABLE Employees(
  Id INT PRIMARY KEY,
  Name VARCHAR2(20) NOT NULL,
  DepartmentID INT REFERENCES Department(Id)
);

INSERT into Employees VALUES (101, 'Anurag', 10); 
INSERT into Employees VALUES (102, 'Pranaya', 20); 
INSERT into Employees VALUES (103, 'Hina', 30);

Example: Referential Integrity Constraints in Oracle

Now, consider the above 2 tables (Department and Employees). If we delete the row with ID = 10 from the Department table then the row with ID = 101 in the Employees table becomes an orphan record. That means we will not be able to tell the Department name for that employee. So, the referential integrity constraint in Oracle is basically used to define the actions that the Oracle server should take. By default, if we try to delete or update the record, we get an error and the DELETE or UPDATE statement is rolled back. Let us prove this. Please try to execute the below DELETE Statement.

DELETE FROM Department WHERE Id = 10;

When you try to execute the above Delete Statement, you should get the error message ORA-02292: integrity constraint (SYSTEM.SYS_C007675) violated – child record found as shown in the below image.

DELETE Rules in Oracle

If you want to delete a record in the parent table (in our case the Department table) when they have a corresponding child record in the child table (in our case the Employees table), Oracle is provided with a set of rules to perform delete operations known as DELETE rules. If we want to delete a record from the parent table when they have corresponding child records in the child table then we provide some set of rules to perform delete operations on the parent table. Those rules are called “CASCADE RULES”.

1. ON DELETE CASCADE
2. ON DELETE SET NULL

Note: The point that you need to remember is, DELETE rules were not imposed on the master table, they are imposed on the child table, and that too on the foreign key column.

CASCADE Referential Integrity Constraints in Oracle

Let’s delete the existing Employees table and again create the Employees table by executing the below SQL Script.

DROP TABLE Employees;
CREATE TABLE Employees(
  Id INT PRIMARY KEY,
  Name VARCHAR2(20) NOT NULL,
  DepartmentID INT REFERENCES Department(Id)
  ON DELETE CASCADE 
);

As you can see in the above SQL Script, we have set the ON DELETE rules as CASCADE. This means if we delete a record from the Department table (Primary Key Table) for which if there are some records exist in the Employees table (Foreign Key Table), then those records will also be deleted. Now, insert some data into the Employees table by executing the below SQL statement.

INSERT into Employees VALUES (101, 'Anurag', 10);
INSERT into Employees VALUES (102, 'Pranaya', 20);
INSERT into Employees VALUES (103, 'Hina', 30);

Now, delete the Department whose Id is 10 from the Department table by executing the below SQL Statement.

DELETE FROM Department WHERE Id = 10;

Now, you can see the above DELETE statement executed successfully, and further if you notice the employees whose DepartmentId is 10 are also deleted from the Employees table automatically. You can verify the same by executing the below SELECT query,

SELECT * FROM Employees;

When you execute the above query, you will see that the employees whose DepartmentId is 10 are also deleted from the Employees table as shown in the below image.

SET NULL Referential Integrity Constraints in Oracle

Let’s delete the existing Employees table and again create the Employees table by executing the below SQL Script. As you can see in the below SQL Script, we have set the ON DELETE rules as SET NULL. This means if we delete a record from the Department table for which if there are some records exist in the Employees table, then those records will also be set as NULL values. First, delete the Employees table by executing the below SQL Statement.

DROP TABLE Employees;

Then truncate the Department table and add the three records by executing the below SQL Statement.

TRUNCATE TABLE Department;
Insert into Department values (10, 'IT');
Insert into Department values (20, 'HR');
Insert into Department values (30, 'INFRA');

Now, create the Employees table by executing the below SQL Statement.

CREATE TABLE Employees(
  Id INT PRIMARY KEY,
  Name VARCHAR2(20) NOT NULL,
  DepartmentID INT REFERENCES Department(Id) ON DELETE SET NULL 
);

Now, insert the following records into the Employees table by executing the below INSERT Statements.

INSERT into Employees VALUES (101, 'Anurag', 10);
INSERT into Employees VALUES (102, 'Pranaya', 20);
INSERT into Employees VALUES (103, 'Hina', 30);

Now, delete the Department whose Id is 10 from the Department table by executing the below SQL Statement.

DELETE FROM Department WHERE Id = 10;

Now, you can see the above DELETE statement executed successfully, and further if you notice the Employees table, those employees whose DepartmentId is 10 are set to NULL automatically. You can verify the same by executing the below SELECT statement.

SELECT * FROM Employees;

When you execute the above query, you will see that the employees whose DepartmentId was 10 are updated as NULL in the Employees table as shown in the below image.

Points to Remember:

If we want to delete a record from the parent table when they have corresponding child records in the child table then we provide some set of rules to perform delete operations on the parent table. those rules are called “cascade rules”.

1. ON DELETE CASCADE
2. ON DELETE SET NULL

ON DELETE CASCADE: Whenever we are deleting a record from the parent table then that associated child records are deleted from the child table automatically.

ON DELETE SET NULL: Whenever we are deleting a record from the parent table then that associated child records are set to null in the child table automatically.

In the next article, I am going to discuss the Data Dictionaries in Oracle with Examples. Here, in this article, I try to explain Referential Integrity Constraint in Oracle with Examples and I hope you enjoy this Referential Integrity Constraint in Oracle article.