SQL Server PRIMARY key FOREIGN KEY constraint

PRIMARY KEY constraint

A table typically has a column or set of columns that contains values that uniquely identify each row in the table. Such a column or columns is called the primary key (PK) of the table and is used to enforce the entity integrity of the table. Because primary KEY constraints guarantee the uniqueness of data, this constraint is often defined on the identity column.

If a PRIMARY KEY constraint is specified for a table, the database engine enforces the uniqueness of the data by automatically creating a unique index on the primary key column. This index also allows quick access to data when a primary key is used in a query. If a primary key constraint is defined on multiple columns, the values in one column may be duplicated, but any combination of the values of all the columns in the discretionary key constraint definition must be unique.

As shown, the ProductID and vendorid columns in the purchasing.productvendor table make up the composite PRIMARY KEY constraint for this table. This ensures that each row in the ProductVendor table has a unique combination of ProductID and VendorID . This prevents duplicate rows from being inserted.

• A table can contain only one PRIMARY KEY constraint.

• The primary key cannot exceed 16 columns and the total key length cannot exceed 900 bytes.

• Indexes generated by primary KEY constraints do not cause the number of indexes in the table to exceed 999 nonclustered indexes and one clustered index.

• If no clustered or nonclustered indexes are specified for the primary KEY constraint, and the clustered index is not in the table, the clustered index is used.

• All columns defined in a PRIMARY key constraint must be defined as NOT NULL. If no nullability is specified, the nullability of all columns participating in the PRIMARY KEY constraint is set to NOT NULL.

• If you define a primary key in a column of a CLR user-defined type, the implementation of that type must support binary ordering.

FOREIGN KEY Constraints

A foreign key (FK) is a combination of one or more columns used to establish and strengthen links between data in two tables to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when one or more of the columns containing the primary key values of one table are referenced by one or more columns in the other table. This column becomes the foreign key of the second table.

For example, because there is a logical relationship between sales orders and sales people, the sales.salesorderheader table contains a foreign key link to the sales.salesperson table. The salespersonid column in the salesorderheader table corresponds to the primary key column in the Salesperson table. The salespersonid column in the SalesOrderHeader table is a foreign key that points to the Salesperson table. By creating a further key relationship, if a foreign key relationship does not exist in the Salesperson table, the value of SalesPersonID cannot be inserted into the SalesOrderHeader table.

Tables can have up to 253 additional tables and columns as foreign key references (outgoing references). SQL Server 2016 increases the limit of the number of additional tables and columns (incoming references) that can be referenced in a separate table from 253 to 10,000. (The compatibility level must be at least 130.) The increase in the number limit brings the following constraints:

• Only a DELETE DML operation supports more than 253 foreign key references. UPDATE and MERGE operations are not supported.

• A table with a foreign key reference to itself can still have only 253 foreign key references.

• More than 253 foreign key references are not supported for Columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables.

Index of the FOREIGN KEY constraint

Unlike primary key constraints, creating a FOREIGN key constraint does not automatically create a corresponding index. However, it is often useful to manually create indexes for foreign keys for the following reasons:

• When you combine data from related tables in a query, you often use foreign key columns in the join condition by matching one or more columns in the FOREIGN KEY constraint of one table with the primary key column or unique key column in the other table. Indexes enable the database engine to quickly find related data in the Foreign key table. However, creating this index is not required. Even if you do not define a primary key or FOREIGN KEY constraint on two related tables, you can combine the data from both tables, but the foreign key relationship between the two tables illustrates that it has been optimized with its key as a condition for grouping into queries.

• Changes to the primary KEY constraint can be checked by a FOREIGN key constraint in the related table.

Referential integrity

Although the primary purpose of a FOREIGN key constraint is to control the data that can be stored in the external key table, it can also control changes to the data in the primary key table. For example, if you delete a salesperson row in the Sales.SalesPerson table, and the salesperson's ID is used by a sales order in the sales.salesorderheader table, The integrity of the association between the two tables is broken; Sales orders for sales people deleted in the SalesOrderHeader table are orphaned because they are not linked to the data in the Salesperson table.

FOREIGN KEY constraints prevent this from happening. If a change in the data in the primary key table invalidates the link to the data in the Foreign key table, the change will not be implemented, ensuring referential integrity. If you attempt to delete a row in a primary key table or change a primary key value that corresponds to a value in the foreign KEY constraint of another table, the operation will fail. To successfully change or delete a row in a foreign key constraint, you must first delete or change the foreign key data in the Outer key table, which links the foreign key to different primary key data.

SQL Server PRIMARY key FOREIGN KEY constraint