Foreign Key Constraint in Oracle

Foreign Key Constraint in Oracle with Examples

In this article, I am going to discuss the Foreign Key Constraint in Oracle with Examples. Please read our previous article where we discussed the Primary Key Constraint in Oracle with examples. At the end of this article, I am going to discuss everything about Foreign Key Constraint in Oracle with Examples.

What is Foreign Key Constraint in Oracle?

Creating the relationships between the database tables is one of the most important concepts in a database. The relationship between multiple tables provides a mechanism for linking the data stores in multiple tables and retrieving them in an efficient manner.

In order to create a link between two tables, we must specify a Foreign Key in one table that references a column in another table. That means the Foreign Key constraint in Oracle is used for binding two tables with each other and then verifying the existence of one table data in other tables.

Note: A foreign key in one TABLE points to either a primary key or a unique key in another table in Oracle. The foreign key constraints are basically used to enforce referential integrity.

How to Create Foreign Key Constraint in Oracle?

To Create a Foreign Key Constraint in Oracle, we require the following things

  1. We require two tables for linking with each other and those two tables must have a common column for binding the tables.
  2. The common column that is present in both the tables need not have the same name but their data type must be the same.
  3. The common column that is present under the parent table or master table is known as the reference key column and moreover, the reference key column should not contain any duplicate values. So, we need to impose either UNIQUE or PRIMARY key constraint on that column.
  4. The common column which is present in the child or detailed table is known as the Foreign key column and we need to impose a Foreign key constraint on the column which refers to the reference key column of the master table.
  5. Foreign key column values should be matched with the primary key or unique key column values only. Generally, the primary key or Unique key is not allowed duplicate values whereas the foreign key is allowed duplicate & null values.

If the above points are not clear at the moment, then don’t worry, we will try to explain all the above points with examples.

Imposing FOREIGN KEY Constraints at Column Level in Oracle.

Syntax: <COMMON COLUMN NAME OF CHILD> <Data Type>[SIZE] REFERENCES <PARENT TABLE NAME>(<COMMON COLUMN NAME OF PARENT>)

Let us understand how to create the primary key and foreign key relationship between two tables in Oracle by imposing FOREIGN KEY Constraints at Table Level. First, we will create a table with the name Department by using the PRIMARY KEY constraint by executing the below CREATE Table query. This table is going to be the parent table or master table which contains the reference key column. Here, we created the reference column (Id) using the Primary Key constraint.

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

Once you created the Department table, now insert some master data into this table by executing the below SQL Script.

INSERT INTO Department (Id, Name, Location) VALUES (10, 'IT', 'Hyderabad');
INSERT INTO Department (Id, Name, Location) VALUES (20, 'HR', 'Delhi');
INSERT INTO Department (Id, Name, Location) VALUES (30, 'Finance', 'Mumbai');

Now. we will create another table with the name Employee by using the FOREIGN KEY constraint by executing the below SQL Script. This table is going to be our foreign key table or child table. Further, if you notice in the below query, we have marked the DepartmentId column as the FOREIGN KEY which references to the Id column of the Department table.

CREATE TABLE Employee 
( 
    ID    INT PRIMARY KEY, 
    Name  VARCHAR2(20), 
    Salary INT, 
    DepartmentId INT REFERENCES Department(Id) 
);

Once you execute the above CREATE Table Statements, the Employee table is created with the Foreign Key Constraints.

Note: The DepartmentId column of the Employee table and the Id column of the Department table must have the same data type otherwise foreign key relationship is not possible. In our case, both columns have the same INT data type.

Now, execute the following INSERT statement to insert three records into the Employee table.

INSERT into Employee VALUES (101, 'Anurag', 25000, 10); 
INSERT into Employee VALUES (102, 'Pranaya', 32000, 20); 
INSERT into Employee VALUES (103, 'Hina', 35000, 30);

When you execute the above insert statements, three records are inserted into the Employee table as expected as shown in the below image.

Foreign Key Constraint in Oracle with Examples

Now, try to execute the following INSERT Statement. Here, we are passing the Department Id value as 40 which actually does not exist in the Department table.

INSERT into Employee VALUES (104, 'Sambit', 52000, 40);

When you try to execute the above INSERT SQL Statement, you will get the error ORA-02291: integrity constraint (SYSTEM.SYS_C007663) violated – parent key not found as shown in the below image. It clearly says that you cannot add or update a child row if the foreign key value does not exist in the parent table. In this case, the foreign key value i.e. 40 that we want to insert in the employee table does not exist in the Department table and hence we got the below error.

Foreign Key Constraint

Rules to Follow while working with Foreign Key in Oracle:

When we impose a Foreign Key constraint and establish the relation between tables in Oracle, the following 3 rules come into the picture.

Rule1:

We cannot insert a value into the foreign key column if that value is not existing in the reference key column of the parent (master) table. For example,

INSERT into Employee VALUES (104, 'Sambit', 52000, 40);

When we try to execute the above INSERT Statement, you will get the error ORA-02291: integrity constraint (SYSTEM.SYS_C007663) violated – parent key not found as shown in the below image.

Foreign Key Constraint in Oracle with Examples

Rule2:

We cannot update the reference key value of a parent table if that the value has a corresponding child record in the child table without addressing what to do with the child records. For example,

UPDATE Department SET Id = 100 WHERE Id = 10;

As we have a child record with department id 10 in the Employee table, so, trying to update the same in the Department table will give you the error ORA-02292: integrity constraint (SYSTEM.SYS_C007663) violated – child record found as shown in the below image.

Foreign Key Constraint in Oracle

Rule3:

We cannot delete a record from the parent table provided that the records reference key value has a child record in the child table without addressing what to do with the child record. For example,

DELETE FROM Department WHERE Id = 10;

As we have a child record with department id 10 in the Employee table, so, trying to delete the same in the Department table will give you the error ORA-02292: integrity constraint (SYSTEM.SYS_C007663) violated – child record found as shown in the below image.

Foreign Key Constraint

Note: If we want to delete or update a record from the parent table when they have corresponding child records in the child table then we need to provide some set of rules to perform the delete and update operations on the parent table. Those rules are called “CASCADE RULES” and that we will discuss in detail in our next article.

How to add Foreign Key Constraints to Existing Table in Oracle?

It is also possible in oracle to add Foreign Key Constraints to the Existing Table. Let us understand this with one example. Let us first, create a table without Foreign Key constraints by executing the below SQL Statement.

CREATE TABLE Employee1 
( 
    ID    INT PRIMARY KEY, 
    Name  VARCHAR2(20), 
    Salary INT, 
    DepartmentId INT
);

Once the table is created, we want to add a Foreign Key on the DepartmentId column which should refer to the Id column of the Department table. You can do the same by using the ALTER TABLE statement as shown in the below query.

ALTER TABLE Employee1 ADD FOREIGN KEY (DepartmentId) REFERENCES Department(Id);

Imposing FOREIGN KEY Constraints at Table Level in Oracle.

While creating a Foreign key constraint at table level we need to use explicitly specify the Foreign key clause whereas it is not required while creating at column-level because the constraint is defined beside the foreign key column only. Following is the syntax to Create FOREIGN KEY Constraints at Table Level.

Syntax: CREATE TABLE <TN>(<COL1><DT>[SIZE],<COL2><DT>[SIZE],……………………….., FOREIGN KEY(<COL1>,<COL2>,……..) REFERENCES <PARENT TABLE NAME>(<COL1>,<COL2>,……………….);

Example:

In the Create Table statements, first, we created all the columns and then we create the FOREIGN KEY constraints and while creating the FOREIGN KEY constraints we need to specify the FOREIGN KEY column i.e. DepartmentId in our table and the Reference table name and column i.e. Department table Id column as Department(Id).

CREATE TABLE Employee2 
( 
    ID    INT PRIMARY KEY, 
    Name  VARCHAR2(20), 
    Salary INT, 
    DepartmentId INT,
    FOREIGN KEY (DepartmentId) REFERENCES Department(Id)  
);
What is the difference between Primary Key and Foreign Key Constraint in Oracle?
Primary Key in Oracle:
  1. The Primary Key Constraint in Oracle is uniquely identifying a record in a table.
  2. Primary Key constraint neither accepts null values nor duplicate values on the column on which it is applied.
  3. We can create only one Primary Key on a table in Oracle and that primary key constraint can be created either on a single column or multiple columns.
Foreign Key in Oracle:
  1. The Foreign Key in Oracle is a column in a table that is a unique key (either primary or unique key) in another table.
  2. A Foreign Key can accept both null and duplicate values.
  3. We can create more than one Foreign key on a table in Oracle.
Can we create a table with multiple unique, foreign, or primary keys in Oracle?

We can create a table in Oracle with multiple unique and foreign keys. But it is not possible to create a table with multiple primary Keys.

Is it possible that a foreign key references a non-primary key in Oracle?

Yes, it is possible. The point that you need to keep in mind is that a foreign key actually references a key that should contain unique values. So, it may be a primary key or a unique key as both keys maintain the uniqueness of the column of a table.

Can a foreign key accept null values in Oracle?

Yes, a foreign key in MySQL can accept NULL values. This is because a Foreign key can reference unique or non-primary keys which may hold NULL values.

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

Leave a Reply

Your email address will not be published.