Foreign key in MS SQL Server

Prerequisite – Primary key in MS SQL Server
SQL Server has different keys which serve a different purpose. In this article, the foreign key will be discussed in brief.

The foreign key has a similar purpose as the primary key yet, the foreign key is used for two tables. In a few cases, the foreign key is used for self-referencing a single table.

Foreign key :
A singular column or a set of columns of one table that uniquely identified by a singular column or a set of columns of another table is referred to as a foreign key.

Syntax –

constraint fk_constraint-name foreign key(col1, col2) 
references parent_table-name(col1, col2)
                                        (OR)
foreign key(col1, col2) 
references parent_table-name(col1, col2)

A foreign key has two tables – parent table and child table. If a user wants to insert a column in a child table, the column has to be a part of the parent table otherwise, an error is displayed. In the syntax, the constraint term is not mandatory to use.



When a foreign key is mentioned in the query, the key automatically creates a referential constraint meaning a column can be inserted in the child table only if it is a part of the parent table.

Two tables named student (parent table) and marks (child table) are considered from the university database.

Table – Student

Name Rollno Age
Aisha 111 18
Maya 112 19
Fatima 113 18


Table – Marks

Name Rollno Marks
Aisha 111 9.5
Maya 112 8.7
Fatima 113 7.7

If a user wants to insert a new column, the query is given as –

foreign key('rollno') 
references student('rollno')

insert into marks ('name', 'rollno', 'marks') 
values('Naina, '111', '7.5')

An error is displayed as the roll number is already taken by a student. (Foreign key constraint). The foreign key does not allow the value to reoccur. To avoid such errors, the values must neither be repeated nor a different column must be considered.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.