Oracle Foreign Key Constraint with Examples

A Foreign Key constraint establishes a link or relationship between columns in one table with column( WHICH IS A PRIMARY KEY) from other table.

A Foreign key constraint enforces referential integrity between two tables, one which is termed as child table in which we add the Foreign Key, which references the primary key in child table.

Let’s have a look at these two tables (Teachers and Students Table) to understand this better:

As we can see, both these tables are linked to each other because of Teacher_ID column in Students table which references back to Teacher_ID column in Teacher table.

Teacher_ID in Teachers table is a primary key while in Students table it’s a foreign key.

In this case relationship between 2 tables can be explained through this,

Students Table : Child Table with Foreign key ( Teacher_ID)
Teachers Table : Parent Table with Primary Key ( Teacher_ID)

Let’s see how to create, alter, drop and enable/disable Foreign key constraints using the following examples.

Adding Foreign Key Constraint Using CREATE TABLE Statement

The syntax to add a Foreign Key constraint using CREATE TABLE Statement is as follows:

Syntax

CREATE TABLE table_name
(
  column1 datatype,
  column2 datatype,
  ...

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);

Let’s create the Students and Teachers table that we saw above and add foreign key relationship between them:

CREATE TABLE teachers
( teacher_id NUMBER(10),
  teacher_name VARCHAR2(50) NOT NULL,
  courses VARCHAR2(50),
  CONSTRAINT teacher_pk PRIMARY KEY (teacher_id)
);

CREATE TABLE students
( student_id NUMBER(10) NOT NULL,
  name VARCHAR2(50) NOT NULL,
  birtha_date DATE NOT NULL,
  CONSTRAINT fk_teacher
    FOREIGN KEY (teacher_id)
    REFERENCES teachers(teacher_id)
);

We can also add foreign key constraint to more than one column at once.

Adding Foreign Key Using ALTER TABLE Statement

We can also add Foreign key constraint to a column in table that already exists using ALTER TABLE statement.

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ...)
   REFERENCES parent_table (column1, column2, ... )

Example:

Let’s add foreign key constraint in Students table using this query:

ALTER TABLE students
ADD CONSTRAINT fk_teacher
   FOREIGN KEY (teacher_id)
   REFERENCES teachers (teacher_id)

Drop Foreign Key

The syntax for dropping Foreign key constraint in a table is as follows:

Syntax

ALTER TABLE child_table
DROP CONSTRAINT constraint_name;

Example:

Let’s write a query to drop foreign key constraint from Students table.

ALTER TABLE students
DROP CONSTRAINT fk_teacher;

Enable/Disable Foreign Key

We can temporarily enable or disable the Foreign key constraint using the following query:

Syntax

ALTER TABLE child_table
[ENABLE/DISABLE] CONSTRAINT constraint_name;

Example:

Enabling or disabling Foreign key constraint allows us to toggle between enforcing referential integrity rules and turning them off. We use the following query to disable the foreign key constraint in Students table.

ALTER TABLE students
DISABLE CONSTRAINT fk_teacher;

Adding Foreign Key with Cascade Delete

A Foreign key constraint with cascade delete ensures that when a row in parent table is deleted then the related field or row in child table will also get deleted.

For example, If we delete a teacher_ID = 30 in Teachers table then the corresponding student who is taught by teacher_ID = 30 in Students table will also get deleted.

Using CREATE TABLE

The syntax for adding Foreign key with cascade delete using CREATE TABLE Statement in Oracle Database is as follows:

Syntax

CREATE TABLE table_name
(
  column1 data_type null/not null,
  column2 data_type null/not null,
  ...

  CONSTRAINT fk_constraint_name
     FOREIGN KEY (column1, column2, ... )
     REFERENCES parent_table (column1, column2, ...)
     ON DELETE CASCADE
);

Example:

Let’s add Foreign key constraint in students table with ON DELETE CASCADE.

CREATE TABLE students
( student_id NUMBER(10) NOT NULL,
  name VARCHAR2(50) NOT NULL,
  birtha_date DATE NOT NULL,
  CONSTRAINT fk_teacher
    FOREIGN KEY (teacher_id)
    REFERENCES teachers(teacher_id)
    ON DELETE CASCADE
);

Using ALTER TABLE

The syntax for adding Foreign key constraint with ON DELETE CASCADE is as follows:

Syntax:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... )
   REFERENCES parent_table (column1, column2, ... )
   ON DELETE CASCADE;

Example:

ALTER TABLE students
ADD CONSTRAINT fk_teacher
   FOREIGN KEY (teacher_id)
   REFERENCES teachers (teacher_id)
   ON DELETE CASCADE;

Adding Foreign Key with ON DELETE SET NULL

When we add a Foreign Key with ON DELETE SET NULL option, Oracle database ensures that when a row in parent table is deleted, the foreign key fields in corresponding row in child table are set to NULL.

For example, If we delete a teacher_ID = 30 in Teachers table then the Teacher_ID field in corresponding Students table will be set to NULL.

Using CREATE TABLE

The syntax for adding Foreign key with ON DELETE SET NULL is as follows:

Syntax

CREATE TABLE table_name
(
  column1 data_type null/not null,
  column2 data_type null/not null,
  ...

  CONSTRAINT fk_constraint_name
     FOREIGN KEY (column1, column2, ...)
     REFERENCES parent_table (column1, column2, ...)
     ON DELETE SET NULL
);

Example:

Let’s try to add foreign key with ON DELETE SET NULL in students table using the following query:

CREATE TABLE students
( student_id NUMBER(10) NOT NULL,
  name VARCHAR2(50) NOT NULL,
  birtha_date DATE NOT NULL,
  CONSTRAINT fk_teacher
    FOREIGN KEY (teacher_id)
    REFERENCES teachers(teacher_id)
    ON DELETE SET NULL
);

Using ALTER TABLE

The syntax for adding Foreign key with ON DELETE SET NULL in a table that already exists is as follows:

Syntax:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n)
   ON DELETE SET NULL;

Example:

Let’s add the Foreign key constraint with ON DELETE SET NULL option in Students table using ALTER TABLE statement:

ALTER TABLE students
ADD CONSTRAINT fk_teacher
   FOREIGN KEY (teacher_id)
   REFERENCES teachers (teacher_id)
   ON DELETE SET NULL;

In this tutorial we learned how to add:

1. Foreign Key Constraint
2. Foreign Key Constraint with ON DELETE CASCADE
3. Foreign Key Constraint with ON DELETE SET NULL

We also understood the differences between each of these types and how to drop and enable/disable them.