Oracle Foreign Key

Summary: in this tutorial, you will learn how to use the Oracle foreign key to establish the relationship between tables.

Introduction to Oracle foreign key constraint

A foreign key is all about the relationship. Let’s start with an example to clearly understand its concept.

Suppose, we have two tables supplier_groups and suppliers:

CREATE TABLE supplier_groups(
    group_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    group_name VARCHAR2(255) NOT NULL,
    PRIMARY KEY (group_id)  
);

CREATE TABLE suppliers (
    supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    supplier_name VARCHAR2(255) NOT NULL,
    group_id NUMBER NOT NULL,
    PRIMARY KEY(supplier_id)
);
Code language: SQL (Structured Query Language) (sql)

The supplier_groups table stores supplier groups e.g., one-time supplier, third-party supplier, and inter-co supplier. Each supplier group may have zero, one, or many suppliers.

The suppliers table stores the supplier information. Each supplier must belong to a supplier group.

The relationship between supplier_groups and suppliers table is one-to-many. In other words, one supplier group has many suppliers while each supplier must belong to a supplier group.

The group_id in the suppliers table is used for establishing the relationship between rows in the suppliers and supplier_groups tables.

Before inserting a row into the suppliers table, you have to look up an existing group_id in the supplier_groups table and use this value for insertion.

Assuming that the supplier_groups table contains the following data:

INSERT INTO supplier_groups(group_name) 
VALUES('One-time Supplier');

INSERT INTO supplier_groups(group_name) 
VALUES('Third-party Supplier');

INSERT INTO supplier_groups(group_name)
VALUES('Inter-co Supplier');

SELECT
    *
FROM
    supplier_groups;
Code language: SQL (Structured Query Language) (sql)

To insert a new third-party supplier, you have to use the group_id 2 as follows:

INSERT INTO suppliers(supplier_name, group_id)
VALUES('Toshiba',1);
Code language: SQL (Structured Query Language) (sql)

It works perfectly fine. However, the following statement also works:

INSERT INTO suppliers(supplier_name, group_id)
VALUES('WD',4);
Code language: SQL (Structured Query Language) (sql)

The supplier_groups table has no row with group id 4 but nothing prevents you from inserting it into the suppliers table, which is an issue.

For example, the following query fails to get all suppliers and their groups:

SELECT
    supplier_name,
    group_name
FROM
    suppliers
INNER JOIN supplier_groups
        USING(group_id);
Code language: SQL (Structured Query Language) (sql)

As you can see, the WD supplier is missing in the result set.

A solution to fix this problem is to use the Oracle foreign key constraint to enforce the relationship between rows from the supplier_groups and suppliers tables.

First, drop the suppliers table:

DROP TABLE suppliers;
Code language: SQL (Structured Query Language) (sql)

Second, recreate the suppliers table with a foreign key constraint:

CREATE TABLE suppliers (
    supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    supplier_name VARCHAR2(255) NOT NULL,
    group_id NUMBER NOT NULL,
    PRIMARY KEY(supplier_id),
    FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)
);
Code language: SQL (Structured Query Language) (sql)

In this statement, the following clause was newly added:

FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)
Code language: SQL (Structured Query Language) (sql)

This clause defines the group_id column in the suppliers table as a foreign key that references to the group_id column of the supplier_groups table.

This way, the constraint is enforced by Oracle. In other words, attempting to insert a row into the suppliers table that does not correspond to any row in the supplier_groups table will fail, as attempting to delete a row from the supplier_groups table where there exist dependent rows in the suppliers table.

The suppliers table is called a child table while the supplier_groups is referred to as a parent table. To extend the parent-child analogy, the primary key value is taken from the parent table (supplier_groups) and is inserted into the child table (suppliers) i.e., the child carries a FOREIGN KEY as the parent’s DNA or genetic code.

By the way, the concept of referential integrity is all about maintaining and enforcing this parent-child relationship.

Oracle foreign key constraint in actions

The following statement works because the supplier_groups table has a row with group_id 1:

INSERT INTO suppliers(supplier_name, group_id)
VALUES('Toshiba',1);
Code language: SQL (Structured Query Language) (sql)

However, the following statement will fail:

INSERT INTO suppliers(supplier_name, group_id)
VALUES('WD',4);
Code language: SQL (Structured Query Language) (sql)

Because the supplier_groups has no row with id 4. The following is the error message:

SQL Error: ORA-02291: integrity constraint (OT.SYS_C0010646) violated - parent key not found
Code language: SQL (Structured Query Language) (sql)

Similarly, attempting to delete a row with group_id 1 in the supplier_groups table will fail:

DELETE
FROM
    supplier_groups
WHERE
    group_id = 1;
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error message:

SQL Error: ORA-02292: integrity constraint (OT.SYS_C0010654) violated - child record found
Code language: SQL (Structured Query Language) (sql)

Because the suppliers table (child table) has a row that references the row which is being deleted.

Oracle foreign key constraint syntax

Oracle allows you to create, add, drop, disable, and enable a foreign key constraint.

Create a foreign key constraint

The following statement illustrates the syntax of creating a foreign key constraint when you create a table:

CREATE TABLE child_table (
    ...
    CONSTRAINT fk_name
    FOREIGN KEY(col1, col2,...) REFERENCES parent_table(col1,col2) 
    ON DELETE [ CASCADE | SET NULL ]
);
Code language: SQL (Structured Query Language) (sql)

Let’s examine the statement in detail.

First, to explicitly assign the foreign key constraint a name, you use the CONSTRAINT clause followed by the name. The CONSTRAINT clause is optional. If you omit it, Oracle will assign a system-generated name to the foreign key constraint.

Second, specify the FOREIGN KEY clause to define one or more columns as a foreign key and parent table with columns to which the foreign key columns reference.

Third, use the ON DELETE clause to specify consequences when the rows in the parent table are deleted.

• ON DELETE CASCADE: if a row in the parent is deleted, then all the rows in the child table that reference the removed row will be deleted.
• ON DELETE SET NULL: if a row in the parent is deleted, then all the rows in the child table reference the removed row will be set to NULL for the foreign key columns.

Unlike the primary key constraint, a table may have more than one foreign key constraint.

Add a foreign key constraint to a table

If you want to add a foreign key constraint to an existing table, you use the ALTER TABLE statement as follows:

ALTER TABLE child_table 
ADD CONSTRAINT fk_name
FOREIGN KEY (col1,col2) REFERENCES parent_table(col1,col2);Code language: SQL (Structured Query Language) (sql)

Drop a foreign key constraint

To drop a foreign key constraint, you use the ALTER TABLE statement below:

ALTER TABLE child_table
DROP CONSTRAINT fk_name;Code language: SQL (Structured Query Language) (sql)

Disable a foreign key constraint

To temporarily disable a foreign constraint, you use the following ALTER TABLE statement:

ALTER TABLE child_table
DISABLE CONSTRAINT fk_name;Code language: SQL (Structured Query Language) (sql)

Enable a foreign constraint

Similarly, you also use the ALTER TABLE statement to enable a disabled foreign key constraint:

ALTER TABLE child_table
ENABLE CONSTRAINT fk_name;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle foreign key constraint to enforce the relationship between tables.