BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL 2005+
SQL 2005+

SQL Server Primary Keys

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:

PartsKeyPartNumberPartNameUnitCost
115COPIPE15mm Copper Pipe1.52
222COPIPE22mm Copper Pipe2.97
310MICCOP10mm Microbore Copper Tube1.17
425STPTAP25x25mm Stop Tap5.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.

EngineerIdPartsKeyUnitsHeld
1118
1215
229
241

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.

Creating a Primary Key Using the SQL Server Management Studio

During development of a new database, SQL Server Management Studio (SSMS) tools can be used to create primary keys. To demonstrate, we will add a primary key to the Engineers table. To begin, open SSMS and expand the Object Explorer so that you can see the Engineers table. Right-click the table name and choose "Design" to open the table designer.

To add a single-column primary key, you simply select the column in question and choose "Set Primary Key" from the Table Designer menu. You can also use the Set Primary Key toolbar button. To try this, ensure that the EngineerId column is selected by clicking the first row selection button. Use the menu option or toolbar button to set the primary key. You will see a small key icon alongside the column definition.

NB: The EngineerId column provides a surrogate key as this information has been added to give a unique reference.

SQL Server Primary Key Definition

If you mistakenly add a primary key, you can remove it using the same menu option or toolbar button. You will notice that the description of the menu item, or the tool tip for the button, is updated to show that they will remove the primary key. Once the key is correct, save the changes to the table design and close the table designer window.

Now that the Engineers table has a primary key, it is impossible to create rows with duplicates in the EngineerId column. To see what happens when you try, open a new query window for the JoBS database and execute the following T-SQL statement:

INSERT INTO Engineers VALUES (1, 'Bill Jones', 19.85, null)

The primary key prevents the new row being created and reports an error. NB: Note the default name of PK_Engineers that has been applied to the new primary key constraint and the error includes a multipart name.

Violation of PRIMARY KEY constraint 'PK_Engineers'. Cannot insert duplicate key in object
'dbo.Engineers'. The statement has been terminated.

If you review the contents of the table you will see that the new row was not stored.

Creating a Composite Key Using the Table Designer

A composite key is a primary key containing more than one column. Once defined, the combined value of all of the primary key columns must be unique but the individual columns may include duplicates. This is the type of key that is required for the EngineerStock table. In this table the EngineerId may be repeated, as may the values in the PartNumber column. However, we want to restrict the data so that the combination of these columns is always unique. This will ensure that there is only one stock figure per engineer and part.

In the table designer for the EngineerStock, select both the EngineerId and PartNumber column. To do this, select the EngineerId column first and then click the row selector button for the PartNumber column whilst holding the Ctrl key. Once both columns are highlighted, click the Set Primary Key toolbar button or menu item. A key icon will appear alongside both columns to indicate that the primary key has been set. You can now save the table and close the designer window.

SQL Server Composite Primary Key Definition

17 August 2008