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.