Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle Foreign Key

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) );

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;
Oracle Foreign Key - suplier_groups table

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);

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

INSERT INTO suppliers(supplier_name, group_id) VALUES('WD',4);

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);
Oracle Foreign Key - querying data

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;

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) );

In this statement, the following clause was newly added:

FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)

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);

However, the following statement will fail:

INSERT INTO suppliers(supplier_name, group_id) VALUES('WD',4);

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

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;

Oracle issued the following error message:

SQL Error: ORA-02292: integrity constraint (OT.SYS_C0010654) violated - child record found

Because the suppliers table (child table) has a row that references to 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 ] );

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 defines one or more column as a foreign key and parent table with columns to which the foreign key columns reference.

Third, use the ON DELETE clause to specify consequence 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);

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;

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;

Enable a foreign constraint

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

ALTER TABLE child_table ENABLE CONSTRAINT fk_name;

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

  • Was this tutorial helpful?
  • YesNo
Previous Oracle PRIMARY KEY
Next Oracle Check Constraint

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.