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
CREATE TABLE table_name (
ID INT NOT NULL,
column_name NUMBER references Master_table(column_name),
);
Example
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
dept_id NUMBER REFERENCES departments(dept_id) );

Out-of-line declaration

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

Syntax
CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
...
CONSTRAINT constraint_name FOREIGN KEY (column1, column2, column3 ... column-N)
REFERENCES parent_table (column1, column2, column3... column-N)
);
Example
CREATE TABLE employees (
emp_id NUMBER,
dept_id NUMBER,
CONSTRAINT employees_fk FOREIGN KEY (dept_id) REFERENCES departments);

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
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column-1, column-2, column-3, ... column-n)
REFERENCES parent_table (column-1, column-2, column-3, ... column-n);
Example
ALTER TABLE employees
ADD CONSTRAINT fk_employee
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id);

Drop

An Alter command drops the foreign key from the table.

Example
ALTER TABLE employees DROP CONSTRAINT employees_fk;

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
CREATE TABLE table_parent (column-1 NUMBER Primary key);
CREATE TABLE table_child (column-2 NUMBER PRIMARY KEY,
column-1 REFERENCES table_parent(column-1) ON DELETE CASCADE);
Example
CREATE TABLE departments (dept_id NUMBER PRIMARY KEY);
CREATE TABLE employees (emp_id NUMBER Primary key,
dept_id NUMBER,
dept_id REFERENCES departments (dept_id ) ON DELETE CASCADE);

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
CREATE TABLE table_parent (column-1 NUMBER Primary key);
CREATE TABLE table_child (column-2 NUMBER PRIMARY KEY,
column-1 REFERENCES table_parent(column-1) ON DELETE CASCADE);
Example
CREATE TABLE departments (dept_id NUMBER PRIMARY KEY);
CREATE TABLE employees (emp_id NUMBER Primary key,
dept_id NUMBER,
dept_id REFERENCES departments (dept_id ) ON DELETE SET NULL);

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 *