It means that you can only insert a row into the child table if there is a corresponding row in the parent table.īesides, the foreign key constraint allows you to define the referential actions when the row in the parent table is updated or deleted as follows: FOREIGN KEY (foreign_key_columns) The foreign key constraint ensures referential integrity. In this example, because of the FOREIGN KEY constraint, SQL Server rejected the insert and issued an error. The conflict occurred in database "BikeStores", table "procurement.vendor_groups", column 'group_id'.
SQL Server issued the following error: The INSERT statement conflicted with the FOREIGN KEY constraint "fk_group". Third, try to insert a new vendor whose vendor group does not exist in the vendor_groups table: INSERT INTO procurement.vendors(vendor_name, group_id) Second, insert a new vendor with a vendor group into the vendors table: INSERT INTO procurement.vendors(vendor_name, group_id) SQL Server FOREIGN KEY constraint exampleįirst, insert some rows into the vendor_groups table: INSERT INTO procurement.vendor_groups(group_name) 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. Second, specify a list of comma-separated foreign key columns enclosed by parentheses after the FOREIGN KEY keyword. In this case, SQL Server will automatically generate a name for the FOREIGN KEY constraint. The constraint name is optional therefore it is possible to define a FOREIGN KEY constraint as follows: FOREIGN KEY (column_1, column2.) REFERENCES parent_table_name(column1,column2.)įirst, specify the FOREIGN KEY constraint name after the CONSTRAINT keyword. The general syntax for creating a FOREIGN KEY constraint is as follows: CONSTRAINT fk_constraint_name In the statement above, the following clause creates a FOREIGN KEY constraint named fk_group that links the group_id in the vendors table to the group_id in the vendor_groups table: CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES procurement.vendor_groups(group_id)Ĭode language: SQL (Structured Query Language) ( sql ) SQL Server FOREIGN KEY constraint syntax The vendors table is called the child table that is the table to which the foreign key constraint is applied. The vendor_groups table now is called the parent table that is the table to which the foreign key constraint references. REFERENCES procurement.vendor_groups( group_id) The following statements drop the vendors table and recreate it with a FOREIGN KEY constraint: DROP TABLE vendors ĬONSTRAINT fk_group FOREIGN KEY ( group_id) To create a foreign key, you use the FOREIGN KEY constraint. To enforce the link between data in the vendor_groups and vendors tables, you need to establish a foreign key in the vendors table.Ī 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).
Similarly, you can also delete a row in the vendor_groups table without updating or deleting the corresponding rows in the vendors table that results in orphaned rows in the vendors table. However, with the current tables setup, you can insert a row into the vendors table without a corresponding row in the vendor_groups table. The relationship between the vendor_groups and vendors tables is one-to-many.įor each row in the vendors table, you can always find a corresponding row in the vendor_groups table. Introduction to the SQL Server foreign key constraintĬonsider the following vendor_groups and vendors tables: CREATE TABLE procurement.vendor_groups (Ĭode language: SQL (Structured Query Language) ( sql )Įach vendor belongs to a vendor group and each vendor group may have zero or more vendors.
#Mysql foreign key constraint how to#
Summary: 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.
0 kommentar(er)
