SQL 2005+

SQL Server Primary Keys (2)

The tenth part of the SQL Server Programming Fundamentals tutorial explains the use and creation of primary keys. A primary key is a column, or group of columns, within a database table that provides a unique reference for each row added to that table.

Surrogate Keys

When no suitable natural key exists for a table, a surrogate key can be used instead. A surrogate key is generally a single-column primary key containing a unique value. Often the value is generated automatically. The use of such a key guarantees a unique and immutable value for every row in the table. However, it can be more difficult to read the information in a table when used in a foreign key.

If the previously described Parts table were updated to include an integer-based surrogate key, the data may appear as follows:

PartsKey	PartNumber	PartName	UnitCost
1	15COPIPE	15mm Copper Pipe	1.52
2	22COPIPE	22mm Copper Pipe	2.97
3	10MICCOP	10mm Microbore Copper Tube	1.17
4	25STPTAP	25x25mm Stop Tap	5.99

If the new surrogate key is used in relationship in the EngineerStock table, the stock data is more difficult to read. To determine the number of units of 15mm copper pipe, the data in both tables must be compared.

EngineerId	PartsKey	UnitsHeld
1	1	18
1	2	15
2	2	9
2	4	1

Surrogate keys add a small number of bytes to the storage requirement for every row in the table in which they are defined. This can increase the overall size of the database. However, if the size of the surrogate key is small, this size increase may be offset by the decrease in size of related tables, when compared to the use of larger natural keys.

Surrogate keys are often defined as integers or globally unique identifiers (GUIDs). In the case of integers, the size of integer column selected is dependant upon the anticipated number of rows that will be required in the table. The number usually starts at one and increments by one for each row added to the table. This is ideal for databases that are only used in on-line scenarios. If a database includes synchronisation for offline use, a GUID may be more appropriate to reduce the risk of duplicated values being created during disconnected use.

Creating a Primary Key

Primary keys can be added to tables using various methods. In this article we will concentrate on two options: adding a primary key using the SQL Server Management Studio graphical user interface tools and creating a key using Transact-SQL statements.

The examples below add primary keys to all of the tables in the JoBS database that we have created over the course of the tutorial. If you have not followed the tutorial, download the script using the link at the top of this page and use it to create the database and some sample data. You should also use this script to recreate the database if you have followed the tutorial so far, as is includes modifications to the schema and data not described in the previous article.