SQL Server: Primary Keys

Learn how to create, drop, disable, and enable a primary key in SQL Server (Transact-SQL) with syntax and examples.

What is a primary key in SQL Server?

In SQL Server (Transact-SQL), a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Create Primary Key - Using CREATE TABLE statement

You can create a primary key in SQL Server with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in SQL Server (Transact-SQL) is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ] [ PRIMARY KEY ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

OR

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in SQL Server (Transact-SQL).

For example:

CREATE TABLE employees
( employee_id INT PRIMARY KEY,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  salary MONEY
);

In this example, we've created a primary key on the employees table that is made up of only one field - the employee_id field.

We could have also created the primary key as follows:

CREATE TABLE employees
( employee_id INT,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  salary MONEY,
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

Next, let's look at how to create a primary key in SQL Server (Transact-SQL) where the primary key is a composite key that is made up of more than one field.

For example:

CREATE TABLE employees
( last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  salary MONEY,
  CONSTRAINT employees_pk PRIMARY KEY (last_name, first_name)
);

In this example, we have created a primary key that is made up of two columns, the last_name and the first_name columns. These two fields would uniquely define the records in the employees table.

Create Primary Key - Using ALTER TABLE statement

You can create a primary key in SQL Server (Transact-SQL) with the ALTER TABLE statement. However, you can only use the ALTER TABLE statement to create a primary key on column(s) that are already defined as NOT NULL. If the column(s) allow NULL values, you will not be able to add the primary key without dropping and recreating the table.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Example

Let's look at an example of how to create a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL).

For example:

ALTER TABLE employees
ADD CONSTRAINT employees_pk PRIMARY KEY (employee_id);

In this example, we've created a primary key on the existing employees table called employees_pk. It consists of the field called employee_id. Again, it is important to note that the employee_id must already be defined as a NOT NULL field for this ALTER TABLE statement to succeed. If the employee_id column allows NULL values, the employees table will have to be dropped and recreated with employee_id defined as a NOT NULL field for the primary key to be created.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE employees
ADD CONSTRAINT employees_pk PRIMARY KEY (last_name, first_name);

In this example, we've created a primary key on the employees table that consists of the last_name and first_name fields. The last_name and first_name fields must be defined as NOT NULL in the employees table for this primary key to be created succesfully.

Drop Primary Key

You can drop a primary key in SQL Server using the ALTER TABLE statement.

Syntax

The syntax to drop a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL).

For example:

ALTER TABLE employees
DROP CONSTRAINT employees_pk;

In this example, we would drop the primary key on the employees table called employees_pk.

Disable Primary Key

You can disable a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL).

Syntax

The syntax to disable a primary key using the ALTER INDEX statement in SQL Server (Transact-SQL) is:

ALTER INDEX constraint_name ON table_name DISABLE;

Example

Let's look at an example of how to disable a primary using the ALTER INDEX statement in SQL Server (Transact-SQL).

For example:

ALTER INDEX employees_pk ON employees DISABLE;

In this example, we would disable the primary key on the employees table called employees_pk.

Enable Primary Key

You can enable a primary key using the ALTER INDEX statement in SQL Server (Transact-SQL).

Syntax

The syntax to enable a primary key using the ALTER INDEX statement in SQL Server (Transact-SQL) is:

ALTER INDEX constraint_name ON table_name REBUILD;

Example

Let's look at an example of how to enable a primary key using the ALTER INDEX statement in SQL Server.

ALTER INDEX employees_pk ON employees REBUILD;

In this example, we're enabling a primary key on the employees table called employees_pk.