FOREIGN KEY constraints in SQL
Table of Content:
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.