Understanding FOREIGN KEY Constraints in SQL: A Complete Guide

Definition: Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

To understand FOREIGN KEY, let's see its use, with help of the below tables:

Customer_Detail Table

c_id	Customer_Name	address
101	Adam	Noida
102	Alex	Delhi
103	Stuart	Rohtak

Order_Detail Table

Order_id	Order_Name	c_id
10	Order1	101
11	Order2	103
12	Order3	102

In Customer_Detail table, c_id is the primary key which is set as foreign key in Order_Detail table. The value that is entered in c_id which is set as foreign key in Order_Detail table must be present in Customer_Detail table where it is set as primary key. This prevents invalid data to be inserted into c_id column of Order_Detail table.

If you try to insert any incorrect data, DBMS will return error and will not allow you to insert the data.

Using FOREIGN KEY constraint at Table Level

Code: Order_Detail

CREATE table Order_Detail(
    order_id int PRIMARY KEY, 
    order_name varchar(60) NOT NULL,
    c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id)
);

Code: Customer_Detail

CREATE table Customer_Detail (
    c_id  int PRIMARY KEY, 
    Customer_Name	 varchar(60) NOT NULL,
    address varchar(60) NOT NULL
);

Using FOREIGN KEY constraint at Column Level

Example:

Code:

ALTER table Order_Detail ADD FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);

In this query, c_id in table Order_Detail is made as foriegn key, which is a reference of c_id column in Customer_Detail table.