{"id":591,"date":"2018-06-01T11:06:30","date_gmt":"2018-06-01T04:06:30","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=591"},"modified":"2020-04-11T20:13:24","modified_gmt":"2020-04-11T13:13:24","slug":"sql-server-foreign-key","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-foreign-key\/","title":{"rendered":"SQL Server FOREIGN KEY"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn how to use the SQL Server foreign key constraint to enforce a link between the data in two tables.<\/p>\n\n\n\n

Introduction to the SQL Server foreign key constraint #<\/a><\/h2>\n\n\n\n

Consider the following vendor_groups<\/code> and vendors<\/code> tables:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> procurement.vendor_groups (\n    group_id<\/span> INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    group_name VARCHAR<\/span> (100<\/span>) NOT<\/span> NULL<\/span>\n);\n\nCREATE<\/span> TABLE<\/span> procurement.vendors (\n        vendor_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n        vendor_name VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n        group_id<\/span> INT<\/span> NOT<\/span> NULL<\/span>,\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Each vendor belongs to a vendor group and each vendor group may have zero or more vendors. The relationship between the vendor_groups<\/code> and vendors<\/code> tables is one-to-many.<\/p>\n\n\n\n
For each row in the  vendors<\/code> table, you can always find a corresponding row in the vendor_groups<\/code> table.<\/p>\n\n\n\n
To enforce the link between data in the vendor_groups<\/code> and vendors<\/code> tables, you need to establish a foreign key in the vendors<\/code> table.<\/p>\n\n\n\n
A foreign key is a column or a group of columns in one table that uniquely identifies a row of another table (or the same table in case of self-reference).<\/p>\n\n\n\n
To create a foreign key, you use the FOREIGN KEY<\/code> constraint.<\/p>\n\n\n\n
The following statements drop the  vendors<\/code> table and recreate it with a FOREIGN KEY<\/code> constraint:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> vendors;\n\nCREATE<\/span> TABLE<\/span> procurement.vendors (\n        vendor_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n        vendor_name VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n        group_id<\/span> INT<\/span> NOT<\/span> NULL<\/span>,\n        CONSTRAINT<\/span> fk_group FOREIGN<\/span> KEY<\/span> (group_id<\/span>) \n        REFERENCES<\/span> procurement.vendor_groups(group_id<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The vendor_groups<\/code> table now is called the parent table<\/strong> that is the table to which the foreign key constraint references. The vendors<\/code> table is called the child table<\/strong> that is the table to which the foreign key constraint is applied.<\/p>\n\n\n\n
In the statement above, the following clause creates a FOREIGN KEY<\/code> constraint named fk_group<\/code> that links the group_id<\/code> in the  vendors<\/code> table to the group_id<\/code> in the vendor_groups<\/code> table:<\/p>\n\n\n
CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES procurement.vendor_groups(group_id)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server FOREIGN KEY constraint syntax #<\/a><\/h2>\n\n\n\n
The general syntax for creating a FOREIGN KEY<\/code> constraint is as follows:<\/p>\n\n\n
CONSTRAINT fk_constraint_name \nFOREIGN KEY (column_1, column2,...)\nREFERENCES parent_table_name(column1,column2,..)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s examine this syntax in detail.<\/p>\n\n\n\n
First, specify the FOREIGN KEY<\/code> constraint name after the CONSTRAINT<\/code> keyword. The constraint name is optional therefore it is possible to define a FOREIGN KEY<\/code> constraint as follows:<\/p>\n\n\n
FOREIGN KEY (column_1, column2,...)\nREFERENCES parent_table_name(column1,column2,..)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, SQL Server will automatically generate a name for the FOREIGN KEY<\/code> constraint.<\/p>\n\n\n\n
Second, specify a list of comma-separated foreign key columns enclosed by parentheses after the FOREIGN KEY<\/code> keyword.<\/p>\n\n\n\n
Third, specify the name of the parent table to which the foreign key references and a list of comma-separated columns that has a link with the column in the child table.<\/p>\n\n\n\n
SQL Server FOREIGN KEY constraint example #<\/a><\/h2>\n\n\n\n
First, insert<\/a> some rows into the vendor_groups<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> procurement.vendor_groups(group_name)\nVALUES<\/span>('Third-Party Vendors'<\/span>),\n      ('Interco Vendors'<\/span>),\n      ('One-time Vendors'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert a new vendor with a vendor group into the  vendors<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> procurement.vendors(vendor_name, group_id<\/span>)\nVALUES<\/span>('ABC Corp'<\/span>,1<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement worked as expected.<\/p>\n\n\n\n
Third, try to insert a new vendor whose vendor group does not exist in the vendor_groups<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> procurement.vendors(vendor_name, group_id<\/span>)\nVALUES<\/span>('XYZ Corp'<\/span>,4<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server issued the following error:<\/p>\n\n\n
The INSERT<\/span> statement<\/span> conflicted with<\/span> the FOREIGN<\/span> KEY<\/span> constraint<\/span> \"fk_group\"<\/span>. The conflict occurred in<\/span> database<\/span> \"BikeStores\"<\/span>, table<\/span> \"procurement.vendor_groups\"<\/span>, column<\/span> 'group_id'<\/span>.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, because of the FOREIGN KEY<\/code> constraint, SQL Server rejected the insert and issued an error.<\/p>\n\n\n\n
Referential actions #<\/a><\/h2>\n\n\n\n
The foreign key constraint ensures referential integrity. It means that you can only insert a row into the child table if there is a corresponding row in the parent table.<\/p>\n\n\n\n
Besides, the foreign key constraint allows you to define the referential actions when the row in the parent table is updated or deleted as follows:<\/p>\n\n\n
FOREIGN KEY (foreign_key_columns)\n    REFERENCES parent_table(parent_key_columns)\n    ON UPDATE<\/span> action<\/span> \n    ON<\/span> DELETE<\/span> action<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The ON UPDATE<\/code> and ON DELETE <\/code>specify which action will execute when a row in the parent table is updated and deleted. The following are permitted actions : NO ACTION<\/code>, CASCADE<\/code>, SET NULL<\/code>, and SET DEFAULT<\/code><\/p>\n\n\n\n
Delete actions of rows in the parent table #<\/a><\/h3>\n\n\n\n
If you delete one or more rows in the parent table, you can set one of the following actions:<\/p>\n\n\n\n
However, with the current tables setup, you can insert a row<\/a> into the  vendors<\/code> table without a corresponding row in the vendor_groups<\/code> table. Similarly, you can also delete a row<\/a> in the vendor_groups<\/code> table without updating<\/a> or deleting the corresponding rows in the  vendors<\/code> table that results in orphaned rows in the  vendors<\/code> table.<\/p>\n\n\n\n