Oracle Foreign Keys

Foreign Keys is a way to ensure data integrity in your Oracle database. A Foreign Keys means that values in one table must also appear in another table.

The reference table is called the parent table, and the table with the foreign key is called the child table. The foreign key in the child table is usually referred to as the primary key in the parent table.

The foreign key can be defined either in the CREATE TABLE statement or in the ALTER TABLE statement.

Using CREATE TABLE

Syntax for creating an external key using the CREATE TABLE operator:

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 created the primary key table of the supplier called supplier_pk. It consists of only one field – supplier_id. Then we created a foreign key named fk_supplier in the products table that refers to the supplier table based on the supplier_id field.

We could also create a foreign key with more than one field, as in the example 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 foreign key called fk_foreign_comp refers to the supplier table based on two fields – supplier_id and supplier_name.

Using ALTER TABLE

Syntax for creating an external key in ALTER TABLE operator:

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

In this example, we created a foreign key named fk_supplier that refers to the supplier table based on the supplier_id field.

Example

Another example of creating a foreign key with more than one field in the example below:

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

Oracle SQL Tutorial: How to Create Foreign Keys