Using Foreign Keys in Oracle

This Oracle tutorial explains how to use Foreign Keys in Oracle with syntax and examples.

What is a foreign key in Oracle?

A foreign key is a way to implement referential integrity inside your Oracle database. A foreign key means that values in one desk must additionally show up in any other table.

The referenced table is known as the parent desk whilst the desk with the overseas key is referred to as the child table. The foreign key in the child desk will typically reference a most important key in the mother or father table.

A overseas key can be described in either a CREATE TABLE assertion or an ALTER TABLE statement.

Using a CREATE TABLE statement

Syntax

The syntax for developing a overseas key the usage of a CREATE TABLE declaration is:

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

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

Example

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);

In this example, we have created a predominant key on the dealer table referred to as supplier_pk. It consists of only one discipline – the supplier_id field. Then we’ve created a foreign key referred to as fk_supplier on the products table that references the supplier table primarily based on the supplier_id field.

We could also create a overseas key with extra than one subject as in the instance below:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
);

In this example, our overseas key known as fk_foreign_comp references the dealer desk based on two fields – the supplier_id and supplier_name fields.

Using an ALTER TABLE statement

Syntax

The syntax for creating a overseas key in an ALTER TABLE statement is:

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

Example

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id);

In this example, we’ve got created a foreign key known as fk_supplier that references the provider table based totally on the supplier_id field.

We ought to additionally create a overseas key with greater than one field as in the instance below:

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name);