PRIMARY KEY Constraints
A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.
A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.
When you specify a PRIMARY KEY constraint for a table, the SQL Server 2005 Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes.
If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.
As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table form a composite PRIMARY KEY constraint for this table. This makes sure that that the combination of ProductID and VendorID is unique.
.gif "Composite PRIMARY KEY constraint")
When you work with joins, PRIMARY KEY constraints relate one table to another. For example, to determine which vendors supply which products, you can use a three-way join between the Purchasing.Vendor table, the Production.Product table, and the Purchasing.ProductVendor table. Because ProductVendor contains both the ProductID and VendorID columns, both the Product table and the Vendor table can be accessed by their relationship to ProductVendor.
See Also
Concepts
Creating and Modifying PRIMARY KEY Constraints
Other Resources
CREATE TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL)
DROP TABLE (Transact-SQL)