Primary and Foreign Keys

Introduction

A primary key is an exclusive data item that allows the user to identify any piece of the record. When you specify a primary key constraint for a table, the Database Engine enforces data exclusivity by automatically creating a unique index for the primary key columns. This is useful to identify the data you need fast and efficiently. For example, you can identify a student in a database full of students with just their ID rather than their details.

A primary key follows these rules

A table can only have one primary key, this is usually the “ID” key. It cannot exceed 16 columns and a key length of 900 bytes. The index generated by a primary key constraint cannot cause the number of indexes on the table to exceed 999 non-clustered indexes and 1 clustered index. If clustered or non-clustered is not specified for a primary key constraint, clustered is used if there no clustered index on the table.

All columns defined within a primary key constraint must be defined as not null. If nullability is not specified, all columns participating in a primary key constraint have their nullability set to not null. If a primary key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. A foreign key is a column or combination of columns that are used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. A link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

Referential Integrity

Referential integrity is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key. Therefore, any primary key field changes must be applied to all foreign keys, or not at all. The same restriction also applies to foreign keys in that any updates (but not necessarily deletions) must be propagated to the primary parent key.

For example, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. Some relational database management systems can enforce referential integrity, either by deleting the foreign key rows as well to maintain integrity or by returning an error and not performing the delete. Which method is used may be determined by a referential integrity constraint defined in a data dictionary.