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.

Leave a Reply

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