Hello Friends, In this tutorial, I am going to tell you about Foreign Key in SQL Server. We will also see how to create a Foreign Key in SQL Server. We have already discussed Primary Key in the previous session.
After Completing this tutorial you will be able to understand:
- Foreign Key in SQL Server.
- How to create a Foreign Key in SQL Server.
Introduction:
Foreign Key is also known as a referencing key. It is used to link two tables together. Foreign Key specifies that the value in one table must appear in the second table also. The relationship between two tables is described by the Primary Key in one table and Foreign Key in another table. The table that contains the Primary key is called the Parent Table and the table that contains the Foreign Key is called the Child Table.
The syntax for creating Foreign Key in SQL Server:
WHERE;
childtable:
The name of the child table that you wish to reference with parent table.
column1, column2:
The columns that you want to create in the child table.
fkname:
The name of the Foreign Key Constraint that you want to create.
childcol1, childcol2, … childcol_n:
The columns in childtable that will reference a primary key in the parent_table.
parenttable:
The name of the parent table whose primary key will be used in the childtable.
parentcol1, parentcol2, … parentcol3:
The columns that make up the primary key in the parent_table. The foreign key will enforce a link between this data and the child_col1, child_col2, … child_col_n columns in the child_table.
ON DELETE
Optional. This specifies that what need to do with childtable when the data from the parent table is deleted. You can set one of the values such as NO ACTION, CASCADE, SET NULL, or SET DEFAULT.
ON UPDATE:
This is also optional Optional. This specifies that what to do with the child data when the data from the parent table is updated. You have the options of NO ACTION, CASCADE, SET NULL, or SET DEFAULT.
NO ACTION:
This specifies that no action is performed with the child data when the data from the parent table is deleted or updated.
CASCADE:
This specifies that the child data is either deleted or updated when the data from the parent table is deleted or updated.
SET NULL:
This specifies that the child data is set to NULL when the data from the parent table is deleted or updated.
SET DEFAULT:
This specifies that the child data is set to their default values when the data from the parent table is deleted or updated.
How to create Foreign Key in SQL Server?
For this let’s create a table that will contain the Primary Key.
You can see in the above table structure Employee table have EmployeeID as a Primary Key. Now suppose I need to store Attendance of these employees. So we can create a relationship like this.
You can see from the above script we have referenced an EmployeeID column from the Employee table.
How to create Foreign Key on multiple columns.
Let’s create an Employee table with Primary Key on a group of columns like EmailID and Mobile No. Here is the script for that.
Now for Referencing these two columns with the EmployeeAttendence table, we need to create the EmployeeAttendence table in the following way.
As you can see from the above script I have referenced EmailID and MobileNo columns from the Employee Table.
Create a Foreign Key after the creation of table:
The syntax for creating Foreign Key with ALTER TABLE Statement is:
WHERE;
childtable:
This specifies the name of the Child Table that we want to reference with Parent Table.
fkname:
fkname specifies the name of the Foreign Key CONSTRAINT.
childcol1, childcol2, … childcol_n:
This specifies the name of the column that we want to reference with parent table.
parenttable:
This specifies the name of the Parent Table that contains the Primary Key.
parentcol1, parentcol2, … parentcol_n:
This specifies the columns marked as Primary Key in Parent Table.
Example:
Conclusion:
Hope you understand the concept of Foreign Key in SQL Server. I would like to have your Comments, Feedback and Suggestions:
View More:
Thank You.
best ed medication peoples pharmacy rx pharmacy online
canada drugs best online pharmacy best drugstore eyeshadow
nearest drugstore ed treatment canadian online pharmacies
save on pharmacy http://pharmacy-onlineasxs.com/ erectile
erectile dysfunction causes pharma canadian online pharmacy
professional pharmacy cheap rx 24 hour pharmacy
online pharmacy drugstore online pharmacy no prescription professional pharmacy
Terrific paintings! This is the type of info that should be shared around the net. Disgrace on Google for now not positioning this post upper! Come on over and visit my web site . Thank you =)
This website certainly has all of the information and facts I wanted concerning this subject and didn’t know who to ask. |
I enjoy what you guys are usually up too. This type of clever work and coverage! Keep up the amazing works guys I’ve incorporated you guys to my own blogroll.
http://coleccion-ctb.org/__media__/js/netsoltrademark.php?d=www.fairporn.net/
Cada vez que nos necesites ahí estaremos con la profesionalidad del Servicio Técnico Oficial que te garantiza el óptimo tratamiento del electrodoméstico así como el uso de recambios originales. Con un ritmo vertiginoso, invita al espectador a participar de un día en la vida de Dani Monfrinotti, un actor sin trabajo que atiende el teléfono en la central de reservas de un restaurante de alta categoría.