SQL 2005+

SQL Server Primary Keys

by Richard Carr, published at http://www.blackwasp.co.uk/SQLPrimaryKeys.aspx

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.

Creating a Primary Key Using Transact-SQL

Primary keys can be generated using T-SQL commands, either at the time of creation of the table or at a later date. To create a constraint when creating a table, the CONSTRAINT clause is used within the parentheses containing the column specifications. To create a simple primary key with a clustered index, use the following syntax:

The constraint-name provides a unique name for the primary key. This name must be a unique name for the entire set of database objects rather than just being unique amongst the primary keys. The comma-separated column list specifies which columns make up the primary key.

NB: This is a simplified syntax for the creation of keys with default settings. Additional settings related to the primary key's index exist and will be examined in a later article describing indexes.

To use the above syntax within the creation of the Engineers table, you could execute the following statement:

CREATE TABLE Engineers
(
    EngineerId INT NOT NULL,
    EngineerName VARCHAR(50) NOT NULL,
    HourlyRate MONEY NOT NULL,
    Photograph VARBINARY(MAX)
    CONSTRAINT PK_Engineers PRIMARY KEY CLUSTERED 
    (
        EngineerId
    )
)

NB: The above statement will fail as the Engineers table already exists.

Altering an Existing Table

A primary key can be added to an existing table using the ALTER TABLE statement with the ADD clause. The syntax for the constraint element is the same as for when creating tables:

ALTER TABLE table-name ADD
CONSTRAINT constraint-name PRIMARY KEY CLUSTERED (column1, column2, ..., columnN)

We can test this syntax by adding a primary key to the Parts table. This table has a natural key in the unique part number. To set this column to be the primary key, execute the following statement:

ALTER TABLE Parts ADD CONSTRAINT PK_Parts PRIMARY KEY CLUSTERED (PartNumber)

JoBS Database Primary Keys

The remainder of the primary keys can now be added to the JoBS tutorial database. Use a combination of the graphical user interface tools and Transact-SQL to add the following primary keys.