Introduction to Foreign Key in SQL

A Foreign Key in SQL refers to a column or a group of columns used to connect two tables from the same database to perform any operations on the contents of the tables. One table’s Foreign key is connected to the primary key (has unique values and is a uniquely identified column in that table) of another table, which is used to allow a relationship between both the tables.

So, if you have 1-to-many or many-to-many relations in the database, foreign keys will be very useful. It acts as a cross-reference between two tables (parent_table and child_table) because it references the primary key of another table. So it establishes a link between parent_table and child_table.

Syntax

Creating a new table with a foreign key requires CREATE TABLE permission in the database.

CREATE TABLE child_Table
(
column_1 datatype [ NULL |NOT NULL ],
column_2 datatype [ NULL |NOT NULL ],
...
CONSTRAINT F_key
FOREIGN KEY (child_column1, child_column2, ... child_column_n)
REFERENCES parent_Table (parent_column1, parent_column2, ... parent_column_n)
[ ON DELETE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ]
[ ON UPDATE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ]
);

Child_Table is the table name which we will create.
column_1, column_2- the columns to be added to the table.
F_key- This is a foreign key constraint.
child_column1, child_column2…child_column_n- It is the name of child_Table columns to reference the primary key in the parent table.
Parent_Table- This is the name of parent_table. The primary key of parent_table is referenced in child_table
ON DELETE- This parameter takes action on child data after the deletion of parent data. SET NULL, NO ACTION, CASCADE, SET DEFAULT are some of the values of this parameter.
ON UPDATE- This is an optional parameter that takes action on the child data after an update on the parent data. SET NULL, NO ACTION, CASCADE, SET DEFAULT are some of the values of this parameter.
CASCADE- We can use this together with ON DELETE and ON UPDATE. After deleting or updating parent data, The child data will either be deleted or updated.

Creating a foreign key in an existing table requires ALTER permission on the table.

Code:

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)

Rules

Foreign keys are not applicable to temporary tables.
Foreign key constraint does not have to be linked only to the primary key of another table; it can also be linked to the UNIQUE constraint of another table.
FOREIGN KEY constraints can reference another column in the same table. This is referred to as a self-reference.
Foreign key constraints can refer to the tables within the same database.
We can also insert NULL values in the child table.
When we insert other than the NULL value into the foreign key constraint, the value must exist in the referenced column; else, a violation message occurred.
The value of the parent’s unique keys cannot be changed if the update rule is RESTRICT and there are one or more dependent rows. However, if the update rule is NO ACTION, parent unique keys can be updated as long as every child has a parent key by the time the update statement completes.

Examples

Say we have two tables, Customers and Orders. The Customers table is having all the customer-related data, and the Orders table having customer order-related data.

Customers

Cust_id	Primary Key
Cust_name
Location

Orders

Order_id	Primary Key
Order_date
Qty
Cust_id	Foreign Key
Total Amount

In the above example, the Cust_id column in the ORDERS table is a foreign key pointing to the Cust_id column in the CUSTOMERS table.

Let’s suppose these tables have the following values.

Customers

1001	Alex	US
1002	Carey	US
1003	Sid	UK
1004	Tom	AUS
1005	Kapil	IND

Orders

78	20-10-2018	5	1002	1200
79	12-10-2017	4	1001	800
80	20-11-2016	2	1005	369
81	09-10-2016	5	1002	258
82	19-08-2016	11	1004	1900
83	30-06-2016	45	1001	2300
84	16-02-2016	7	1001	890
85	02-01-2016	2	1002	260

For cust_id 1001 there are three orders that exist in the orders table.

For cust_id 1003 there is no order.

So if we have particular data(Say id 1003) in the parent table, then it is not necessary to have that data in the child table, but vice versa is not true.

We cannot have any data in the Child table(Orders Table), which does not exist in the parent table(Customers.)

For example, we cannot insert a new record, say for cust_id 1006, in the Orders table because cust_id 1006 does not exist in the Customers table.

So below are the examples that violate the referential integrity of this relationship:

Inserting a row in the ORDERS table where Cust_ID does not appear in the Cust_ID column in the CUSTOMERS table.
Deleting a row from the CUSTOMERS table where the Cust_ID of the row to be deleted is still present in the Cust_ID column in the ORDERS table.

When a particular record is deleted from the main table, there are two ways to maintain the integrity of data in the child table. First, when two tables are connected with Foreign Key, and certain data in the main table is deleted, for which the record exists in the child table, then we have some mechanism to save the integrity of data in the child table.

On Delete Cascade: This will remove the record from the child table if that value of the foreign key is deleted from the main table.
On Delete NULL: This will set all the values in that record of the Child table as NULL, for which the value of the foreign key is deleted from the main table.

Conclusion – Foreign Key in SQL

So it is advisable to use Foreign Key in the database, which has one to one or one to many relationships. The main advantage of using foreign key constraints is that it improves performance. Developers can easily identify the structure of the database. We can also examine how the query is going to retrieve the data.

Quiz Result
Total Questions	Correct Answers	Wrong Answers	Percentage

Introduction to Foreign Key in SQL

Rules

Examples

Conclusion – Foreign Key in SQL

Recommended Articles

Follow us!

APPS

Blog

Courses

Email