SQL Foreign key

Overview: In this Tutorial You will understand what is SQL FOREIGN KEY, Use of Foreign Key. A FOREIGN KEY is a field in first table that refers to the PRIMARY KEY in Second table.

What is a Foreign Key in SQL?

In SQL server, a Foreign Key in one table used to point or link to another table with primary key. The table containing the foreign key is called the child table and the table containing the Primary key is called parent table. in another word A column in first table point B column in Second table.

Use of Foreign Key

There is important use of Foreign key, which is use to maintain the referential integrity of data and improve the performance of relational tables. Referential integrity refers to the relationship between two or more than two tables. Because SQL tables have a primary key and when a primary key from one table appears in another table, it is called a foreign key.

lets understand with the below image

In below image Customer Table we have Primary Key as Customer_ID and Foreign key in Order Table as column = Customer ID

Customer ID = 3 is referenced in Order Table. If customer_ID = 3 is deleted from the customer table, the link between the two tables and this particular customer is destroyed.

Syntax of Foreign Key

CREATE TABLE Table_name( 

             column1    datatype1,

             column2    datatype1,  

             constraint (name_of_constraint) 

             FOREIGN KEY [column1, column2..] 

             REFERENCES [primary_key_table_name] (primary_key_table_columns_name) ..);

Example: Create Foreign Key for table

lets consider two tables from above image1.

Look, the “Customer ID” column in the “Orders” table points to the “Customer ID” column in the “Customer” table.

The “Customer ID” column in the “Customers” table is the PRIMARY KEY

The “Customer ID” column in the “Orders” table is a FOREIGN KEY

we will use CREATE TABLE command to create Foreign Key

SQL server Code

CREATE TABLE Orders_Tbl (
             [Order ID] int NOT NULL PRIMARY KEY,
             [Customer ID] int NOT NULL,
             CONSTRAINT FK_Cust_ID
             [Customer ID] int FOREIGN KEY REFERENCES Customer_Tbl.[Customer_ID]
             );

So here you can se we used syntax to define Foreign Key for Customer ID via creating order table

DROP Foreign Key Constraint

In SQL server we use ALTER TABLE statement to drop a foreign key from table, below is the syntax

Syntax:

ALTER TABLE table_name
DROP CONSTRAINT Foreign_Key_name;

table_name
This is the table name where the foreign key has been created.

Example: Drop Foreign Key

Lets drop Foreign key which we have created in above first example, with Foreign Key name “FK_Cust_ID“

To drop constraint we use DROP command.

ALTER TABLE Orders_Tbl 
DROP CONSTRAINT FK_Cust_ID;

Conclusion:

Quick summary. Firstly, we understand what is SQL foreign key. A foreign key constraint which use to maintain a relationship between two SQL tables. learned the syntax and how we can create a foreign key using CREATE TABLE statements and also how to drop foreign key using the DROP statement