How To Create Foreign Key In Sql Developer

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              )

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

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

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 use 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.

Was this tutorial helpful?