Foreign Key In SQL With Examples

What is foreign key in database?

A foreign key is a column or set of columns used to establish a relation between two tables. Sometimes, it is referred as a reference key. The table in which foreign key is created is called child table. And the table to which it references is called a parent table. The term for the foreign key in relational database terminology is referential integrity constraint.

Note: The column or set of columns declared as a reference key in child table should be a primary key in a parent table.

A single table can consist of multiple foreign keys. For more than one reference key single column can be used.

Syntax and Example

There are two ways to declare foreign key 1) Inline declaration 2) Out-of-line declaration

Inline declaration

The column is defined as foreign key at column level in an inline declaration. You can define only single column as a reference key here.

Syntax

Example

Out-of-line declaration

The column is defined as foreign key at table level in an out-of-line declaration.

Syntax

Example

Create using Alter command

Reference key can be created using an alter command. This is helpful when you have decided to not create it initially during the creation of the table.

Syntax

Example

Drop

An Alter command drops the foreign key from the table.

Example

On Delete Cascade

Creating a reference key with ON DELETE CASCADE option will delete all the referenced rows from child table if rows are removed from the parent table.

Syntax

Example

On Delete Set Null

Creating a reference key with ON DELETE SET NULL option will set all the referenced rows from child table to NULL if rows are removed from the parent table.

Syntax

Example

Advantages of Foreign Key

  • Reduce data redundancy
  • Prevents entry of ambiguous data
  • Maintains referential integrity

Read more about constraints at this link.

If you liked the above post, please leave your comments below.

Subscribe here to stay updated on latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.