This index also permits fast access to data when the primary key is used in queries. When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. Because primary key constraints guarantee unique data, they are frequently defined on an identity column. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table.

This topic contains the following sections.Ī table typically has a column or combination of columns that contain values that uniquely identify each row in the table. Primary keys and foreign keys are two types of constraints that can be used to enforce data integrity in SQL Server tables. The maximum number of FOREIGN KEY constraints in a table is 63.Ī definition of the foreign keys in tables of a database imposes the specification of another important integrity constraint: the referential integrity.Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance The FOREIGN KEY clause can be omitted if the foreign key is defined as a column-level constraint, because the column being constrained is the implicit column “list” of the foreign key, and the keyword REFERENCES is sufficient to indicate what kind of constraint this is. Further, the constraint foreign_works specifies the employee table as the parent table and its emp_no column as the corresponding primary key of the column with the same name in the works_on table. Both constraints are table-level constraints, where the former specifies the primary key and the latter the foreign key of the works_on table. The works_on table in Example 1 is specified with two declarative integrity constraints: prim_works and foreign_works. You have to drop theworks_ontable before you execute the following example. Example 1 shows the specification of the foreign key in the works_on table of the sample database. The table that contains the foreign key is called the referencing table, and the table that contains the corresponding primary key is called the parent table or referenced table. The number and the data types of the columns in the FOREIGN KEY clause must match the number and the corresponding data types of columns in the REFERENCES clause (and, of course, both of these must match the number and data types of the columns in the primary key of the referenced table). The REFERENCES clause specifies the table name with all columns that build the corresponding primary key. The FOREIGN KEY clause defines all columns explicitly that belong to the foreign key.

The FOREIGN KEY clause has the following form: Each foreign key is defined using the FOREIGN KEY clause combined with the REFERENCES clause. A foreign key is a column or group of columns in one table that contains values that match the primary key values in the same or another table.

Sql server foreign key constraint