Foreign Key in Oracle Database

By Manish Sharma

Foreign Key.

Oracle Database Foreign Key Explained in Detail

In the last tutorial we learnt about Primary key constraint and today I would like to elaborate on another topic in Oracle database that is referential integrity constraint or Foreign Key.

What is Foreign key Constraint?

Foreign key is an Input/output data constraint which is also known as referential integrity constraint.
Foreign key represents a link or say a relationship between columns of tables.

Types of Foreign Key constraint

Similar to primary key constraint Foreign Key constraint is also of two types:

  1. Simple Foreign key constraint and
  2. Composite Foreign key constraint.

What are Simple and Composite Foreign key constraints?

Constraint which involves only one column in foreign key in child table and one column in reference key in parent table is called Simple Foreign Key. While the constraint which involves more than one column in foreign key in the child table and more than one column in reference key in the parent table is called Composite Foreign Key

Features of Foreign Key

There are a few things which you should know about the foreign key (Features of Foreign Key):

  1. You cannot define a foreign key constraint in a CREATE TABLE statement that contains an AS sub query clause. Instead, you must create the table without the constraint and then add it later with an ALTER TABLE statement.
  2. None of the columns in the foreign key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE.
  3. A composite foreign key cannot have more than 32 columns.
  4. Referenced key in parent table must either be a Primary Key or a Unique Key.
  5. Records in parent table cannot be updated if child record exists.
  6. Foreign Key Constraint can be specified only on child table and not on parent table.

Tables of Foreign Key Constraint

Foreign key involves two different tables. First one is PARENT TABLE or referenced Table and the second one is CHILD TABLE or foreign table.
  1. The column of parent table which will get referenced by foreign key must be either Primary Key or Unique Key.
  2. Column(s) in child table can contain NULL or Duplicate values while the vice versa is not true.
  3. Column(s) of parent table & column(s) of child table which are participating in foreign key should be of the same data-type and size (column width).

How to create/ Define Foreign key

You can define foreign key constraint either by using CREATE TABLE statement or by using ALTER TABLE statement.

sql foreign key manish sharma

1. Defining Foreign Key Using Create table at Column Level

This way of defining constraint is called column level because we define the constraint with column definition while creating table.

Syntax  Column_name   Datatype(size)   REFERENCES   parent_table_name (parent_column_name)

For example

To demonstrate this we will use two tables – parent table with the name of Authors and child table with the name of Books. Parent table ‘authors’ is a simple table with 2 columns Author_id and Author_name where Author_id is a Primary key column. You can add as many columns as you want. Read how to create table and how to define primary key on a table.

  author_id   NUMBER(3)   CONSTRAINT   athr_aid_pk   PRIMARY KEY,
  author_name   VARCHAR2(30)

Now let’s create our child table ‘BOOKS’. The structure of this table contains various columns – book_id which will be the primary key for this table, book_title, book_price and book_author_id. The 4th column will be the foreign key which will reference the author_id column of author table. You can give whatever name to this column but data-type and the size (column width) must be the same as that of author_id column in author table.

  book_id   NUMBER(3),
  book_title   VARCHAR2(30),
  book_price   NUMBER(3),
  book_author_id    NUMBER(3)   CONSTRAINT   bok_ai_fk   REFERENCES   author(author_id)

2. How to define foreign key using CREATE TABLE at table Level

In order to define foreign key Using create table at table level you have to define all the columns of your child table first. Then you have to define foreign key at the end of the table.

Let’s see the syntax first

CONSTRAINT   constraint_name   FOREIGN KEY(child_table_column)   REFERENCES   Parent_table_name(parent_table_column)

To demonstrate how to define foreign key using create table at table level I’ll recreate our child table called BOOKS.

  book_id  NUMBER(3)  CONSTRAINT  bok_bi_pk  PRIMARY KEY,
  book_title   VARCHAR2(30),
  book_price  NUMBER(3),
  book_author_id   NUMBER(3),
  CONSTRAINT  bok_ai_fk  FOREIGN KEY  (book_author_id) REFERENCES  author(author_id)

As you can see I defined all the columns first and then in the last statement of Create table I defined the foreign Key constraint.

Define Foreign Key Using ALTER TABLE statement

We define Foreign Key through ALTER TABLE statement in the scenario when we already have a table and want to emphasize the constraint over it.

Suppose we have a simple table with primary key by the name of BOOKS [Please watch my tutorial on How to create table to learn all about it] and now we want to emphasize the FOREIGN KEY constraint on this table. In this case we will use ALTER TABLE statement.

Let’s how
First let’s go through the syntax –

ALTER TABLE  child_table_name  ADD  FOREIGN KEY  (child_column)  REFERENCES  parent_table_name(parent_column)
For Example

ALTER TABLE  books  ADD  CONSTRAINT  bok_ai_fk  FOREIGN KEY  (book_author_id) REFERENCES  author(author_id);

That’s it.

If you try to delete a parent table which has a primary or a unique key referenced by child table then oracle will give you a SQL Error: ORA-02449. However if you want then you can still drop the child table without any error.

So there you are! This is all about the foreign key concept. Please remember that there are many small topics that branch out from this main foreign key concept and are beyond the scope of this article. Nevertheless hope you liked it. Please share this blog on Foreign Key in Oracle Database with your friends and colleagues.

Also you can visit my YouTube channel for the video tutorial explaining foreign key concept.

Thanks & have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn

         View Manish Sharma's profile on LinkedIn