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.
Definition
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:
- Simple Foreign key constraint and
- Composite Foreign key constraint.
What Are Simple & 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 as well as 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):
- 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.
- 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.
- A composite foreign key cannot have more than 32 columns.
- Referenced key in parent table must either be a Primary Key or a Unique Key.
- Records in parent table cannot be updated if child record exists.
- 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.
- The column of parent table which will get referenced by foreign key must be either Primary Key or Unique Key.
- Column(s) in child table can contain NULL or Duplicate values while the vice versa is not true.
- 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. 
- 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.
CREATE TABLE author
(
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.
CREATE TABLE books
(
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)
);
- 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.
CREATE TABLE 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 see 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);
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.
You can also visit my YouTube channel for the video tutorial explaining foreign key concept. Thanks & have a great day!
