Beginners Guide: Foreign Key in SQL Server

Neerajprasadsharma
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 652 red flag
Rating: 5 out of 5  
 1 vote(s)

Learn what is foreign key in SQL Server, what is the need to have a foreign key and how to create it

Introduction

SQL Server is a relational database that means, we can create a parent, child like relation using the combination of primary/unique key  and foreign key.
 We cannot imagine a child without parent same rules apply here like a table containing the foreign key can`t have data (in foreign key column) Which is not in its parent table (Primary/Unique  key column). 
The Primary/Unique  key is unique per row, but  foreign keys can have more than one row in the child table.


Why use Foreign key:
Enforce Parent Child Relationship (Referential Integrity): 
As we have already specified that the main aim of introducing the foreign key in SQL is to create a parent child relationship between the tables, this guarantee that the referenced rows exist in the parent table.

Cleanup Of Child Tables:
On delete cascade in SQL Server provide the facility to delete the row(s) in child table when the row is deleted in the parent table, so you no need to delete manually from all the child tables.

On update cascade in SQL Server provide the facility to update child`s foreign key row(s) as well, when the parent row is updated in the parent table.

•      With the foreign key constraint, SQL Server creates Non Clustered index on the foreign key column(s), which benefits for the fast lookup when you join the unique/primary key with foreign key table using the key. This point required some more attention and detail. We will write more about this point and will link below the article.

Now we know that to create a foreign we must have a primary /Unique key , so let`s start the example by creating primary key column:

CREATE TABLE TablePrimaryKey ( RollNo int constraint PK_RollNo primary key , Name Varchar(25) )




Now, we have a table named TablePrimaryKey containing  RollNo as a Primary Key column, so now we can create a Foreign Key on the basis on the Primary Key,  let us  create Foreign Key.
Create Foreign Key Syntax: 

Create Table Table_Name (Column_Name DataType Constraint  Constraint_Name  Foregin Key Reference ParentTableName(ColumnWithPrimaryKey)    ,Column_name2 DataType ,...) 
 

Now let us create a table with foreign key using the above syntax:

Create TAble TableForeignKey
(RollNo int CONSTRAINT fk_RollNo FOREIGN KEY REFERENCES TablePrimaryKey(RollNo),
Class varchar(10) , Marks int






In the above example, TableForeignkey is a child table of TablePrimaryKey. We can drop and reestablish this relationship at any time. While reestablishing the parent child relationship, make sure child foreign key`s all rows should already in the parent`s primary key`s column otherwise it will throw an error.
Let us drop the foreign key column from the table.
Drop Foreign Key Syntax:



Drop Foreign Key Syntax:

Alter Table TableName Drop Constraint ConstraintName 
 
Let us use this syntax in our example and drop the Foreign Key constraint.

Alter table TableForeignKey drop constraint fk_RollNo
 


Now let us create a parent child relationship again using the below syntax. 

Alter Table Add Foreign Key Syntax:

Alter Table TableName Add Constraint ConstraintName Foreign Key (ColumnName) Reference ParentTableName(ColumnName) 
 
Let us put this in our table
ALTER TABLE TableForeignKey ADD CONSTRAINT fk_RollNo  FOREIGN KEY (RollNo)
REFERENCES TablePrimaryKey (RollNo) 
 



Above command has established the parent child relationship again between TablePrimaryKey and TableForeignKey table.

So far we have created a parent child relationship using only Primary Key and Foreign Key, however, we can establish this relationship using Unique Key and Foreign Key as well. We will see this in the below example:



Create Table TableForeignKey
(RollNo int CONSTRAINT fk_RollNo FOREIGN KEY REFERENCES TablePrimaryKey(RollNo),
 Class varchar(10) , Marks int


Now let us create a child table with a foreign key.

Create Table FK
(RollNo int CONSTRAINT fk_RollNo FOREIGN KEY REFERENCES TableFK(RollNo),
Class varchar(10) , Marks int


Conclusion

The above article is the introduction to the foreign key concepts and how to create it on SQL Server, in the second part of this article we will learn how to implement  Delete Cascade and Update Cascade in SQL Server.




Page copy protected against web site content infringement by Copyscape

About the Author

Neerajprasadsharma
Full Name: NEERAJ Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.

Neeraj Prasad Sharma is a SQL Server developer who started his work as a dot net programmer. He loves SQL Server query optimizer`s capability to process the queries optimally. For the last six years he has been experimenting and testing Query Optimizer default behaviour and if something goes wrong his goal is to identify the reason behind it and fix it. I write technical article here: https://www.sqlshack.com/author/neeraj/ https://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=12524731 https://www.mssqltips.com/sqlserverauthor/243/neeraj-prasad-sharma-/

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)