Primary Keys in SQL Server – Definition, Syntax, Examples

Understanding the primary key definition in SQL Server is essential for designing tables where each record is unique. The primary key enforces uniqueness by rejecting duplicates, blocking NULLs, and keeping data consistent across the table. 

This makes the primary key both a safeguard for integrity and a driver of performance in SQL Server databases. In this article, we’ll look at the rules behind primary keys, how to create them in different ways, and how they work in practice. 

Table of contents

• Primary key: definition and constraints
  • Primary key definition in SQL Server
  • Primary key vs. foreign key vs. unique key (brief overview) 
• Ways to create a primary key in SQL Server
  • How to create a single primary key in SQL Server
  • Create table with primary key in SQL Server (example)
  • SQL Server primary key constraint syntax
  • How to create a single primary key using the ALTER TABLE statement
  • How to create a composite primary key in SQL Server
  • SQL Server add composite primary key with ALTER TABLE
• How to modify or delete primary keys
  • Drop primary key in SQL Server
  • Alter table to change primary key in SQL Server 
• Primary key vs. unique key 
• Using dbForge Studio for SQL Server to create and manage primary keys 
• Conclusion 

Primary key: definition and constraints 

This section defines the primary key, distinguishes it from other key types, and lists the SQL Server rules it must meet. 

Primary key definition in SQL Server 

The primary key in SQL Server is a column or a combination of columns in a database table that serves as a unique identifier for each row. By applying the SQL primary key constraint, SQL Server enforces uniqueness and prevents NULL values in the designated column(s). At the same time, it automatically creates a unique index on the table data to improve query performance. 

The primary key has the following characteristics:

• Only one primary key is permitted per table.
• The primary key name format should be PK_<TableName>.
• The maximum length of columns in a primary key is 900 bytes.
• Values within the primary key must be unique; duplicates are not allowed.
• All columns in a primary key must be set to NOT NULL.
• Creating a primary key also creates a unique clustered index on the included column(s).

A primary key that consists of only one column is referred to as a single primary key. When the key comprises multiple columns, it is known as a composite primary key.

Primary key vs. foreign key vs. unique key (brief overview) 

These three constraints often get mentioned together, but they solve different problems: 

• Primary key: Defines the main identifier of a table. It’s the foundation that guarantees every row is unique.
• Unique key: Adds extra rules of uniqueness on other columns, useful when you need multiple distinct values beyond the primary key.
• Foreign key: Connects tables by linking a column in one table to the primary key of another, enforcing relational integrity. 

Ways to create a primary key in SQL Server

There are two main ways to define a primary key in SQL Server: 

1. When creating a table using the CREATE TABLE command.
2. After the table already exists, by using the ALTER TABLE command. 

Both approaches enforce uniqueness and NOT NULL rules, but the syntax and use cases differ.  

See the ALTER TABLE section for how to add primary key in SQL Server on an existing table. 

How to create a single primary key in SQL Server

To establish a single primary key, you must designate a single table column as the PRIMARY KEY(COLUMN_NAME). When crafting a key that consists of just one column, you can apply the PRIMARY KEY constraint directly as a column attribute.

Note: If the table already contains data, make sure the chosen column has unique, non-null values. Otherwise, SQL Server will return an error when you try to add the constraint.

Create table with primary key in SQL Server (example) 

If you want the SQL Server CREATE TABLE primary key defined right from the start, you can specify it inline when creating the table. This approach is the most straightforward, since it enforces uniqueness and indexing as soon as the table is created. 

CREATE TABLE AdventureWorks2022.HumanResources.Training (
  Employee_ID INT PRIMARY KEY IDENTITY
 ,Name VARCHAR(50) NOT NULL
 ,Last_Name VARCHAR(50) NOT NULL
 ,Course_Title VARCHAR(250) NOT NULL
 ,Start DATE NOT NULL
 ,Finish DATE NOT NULL
);

Here, the Employee_ID column acts as the primary key, which means it must always contain unique, non-null values. 

SQL Server primary key constraint syntax 

The primary key SQL Server CREATE TABLE syntax is as follows. 

CREATE TABLE table_name ( 
  column_name data_type PRIMARY KEY -- applies the sql primary key constraint 
); 

This declares the key at creation, so the table starts with enforced uniqueness, no NULLs, and a supporting unique index (clustered by default unless specified otherwise). 

How to create a single primary key using the ALTER TABLE statement

Assume we created the HumanResources.Courses table without setting a primary key. But we want to have it in that table and make the Employee_ID column the primary key. To do that, we use the following command. 

-- Syntax 
ALTER TABLE table_name 
ADD CONSTRAINT PK_table PRIMARY KEY (column_name); -- alter table add primary key sql server 

ALTER TABLE HumanResources.Courses
ADD CONSTRAINT PK_Courses PRIMARY KEY(Employee_ID);

Here, the statement demonstrates how to add a primary key in SQL Server after the column in a table that already exists.

Note: If the table contains data, it’s crucial to ensure that the column you intend to designate as the primary key holds unique and non-null values before applying the constraint. Otherwise, executing the command will lead to an error.

How to create a composite primary key in SQL Server

As previously defined, a composite primary key in SQL Server is formed by utilizing multiple columns as the key. This approach proves valuable when a table requires distinct identification of records, yet none of its columns possess unique values. In such scenarios, a composite primary key can be established by combining columns to produce a unique value. Moreover, the columns incorporated within the composite primary key can store different data types.

Create a composite primary key using the CREATE TABLE statement

Let’s consider new HumanResources.TrainingCourses table, involving columns like Employee_ID, Name. Last_Name, Course_Title, Start, and Finish. Given that these columns could potentially hold duplicate values individually, assigning a single primary key to any one column is not feasible. However, by combining values from these columns, we can receive a unique value identifying each record.

The primary difference in the CREATE TABLE command syntax will be a separate line that lists all columns involved in the composite primary key.

CREATE TABLE AdventureWorks2022.HumanResources.TrainingCourses (
  Employee_ID VARCHAR(50) NOT NULL
 ,Name VARCHAR(50) NOT NULL
 ,Last_Name VARCHAR(50) NOT NULL
 ,Course_Title VARCHAR(250) NOT NULL
 ,Start DATE NOT NULL
 ,Finish DATE NOT NULL
 ,CONSTRAINT PK_TrainingCourses PRIMARY KEY CLUSTERED (Employee_ID, Last_Name, Course_Title)
);

Hence, we have implemented a composite primary key across three columns.

SQL Server add composite primary key with ALTER TABLE 

The ALTER TABLE ADD CONSTRAINT command allows developers to incorporate a composite primary key into the already existing table, in the same way as we described earlier for the single primary key.

-- Syntax 
ALTER TABLE schema_name.table_name 
ADD CONSTRAINT PK_table_name  
  PRIMARY KEY (col1, col2, col3); -- sql server add composite primary key 

ALTER TABLE AdventureWorks2022.HumanResources.Training 
ADD CONSTRAINT PK_Training PRIMARY KEY CLUSTERED (Employee_ID, Last_Name, Course_Title);

Note: If duplicates or NULLs exist in any of the key columns, the statement will fail. 

How to modify or delete primary keys

SQL Server does not allow modifying an existing primary key directly. You need to drop it and then configure a new one. This is often done alongside other schema adjustments, such as changing a column’s data type. If needed, you can change the data type in SQL using the ALTER TABLE … ALTER COLUMN command before redefining the primary key. This step ensures that the column is structurally compatible with primary key constraints.

Drop primary key in SQL Server 

To remove a primary key from the table, use the ALTER TABLE command:

ALTER TABLE table_name  
DROP CONSTRAINT constraint_name;  

Assume we need to change the existing single primary key in a table to a composite primary key. The first step is dropping the existing single primary key:

ALTER TABLE HumanResources.Training
DROP CONSTRAINT PK_Training;

Note that dropping a primary key does not delete the data stored in the table or affect it in any other way; it only removes the identification of the records stored in the table.

Now we can re-create a primary key in the same way as shown above, using ADD CONSTRAINT in the ALTER TABLE command.

Alter table to change primary key in SQL Server 

To “change” a primary key, drop the old one and create the new definition (single or composite).

-- Example: switch from single-column PK to composite PK 
ALTER TABLE HumanResources.Training 
DROP CONSTRAINT PK_Training; 
 
ALTER TABLE HumanResources.Training 
ADD CONSTRAINT PK_Training 
  PRIMARY KEY CLUSTERED (Employee_ID, Last_Name, Course_Title); 

Tip: If the table already has a clustered index you want to keep, declare the primary key as NONCLUSTERED.

Primary key vs. unique key

A unique key is an additional constraint that can be employed within a database table to distinguish records. Essentially, it ensures enforcing unique constraints on a column or a group of columns in a table without designating a primary key.

The primary motivation for using a unique key arises when a primary key is already established within the same table. While a table can have just a single primary key, it can possess multiple unique keys.

More differences between the primary and unique keys include:

• NULL value acceptance: Unique keys differ from primary keys in that they can accept NULL values.
• Number of keys: While a table can have only one primary key, it can accommodate several unique keys.
• Modification convenience: Unique keys offer the advantage of direct modification without the need to drop and recreate them.
• Indexes: A unique key establishes a non-clustered index, whereas a primary key establishes a clustered index.

A primary key can be a unique key, but not vice versa.

Using dbForge Studio for SQL Server to create and manage primary keys 

We previously illustrated how to create and manage primary keys using T-SQL statements in dbForge Studio for SQL Server to compose and execute these queries. However, dbForge Studio for SQL Server is not just an interface for SQL coding. It’s an all-embracing integrated development environment for all database-related tasks in SQL Server. Moreover, it greatly simplifies routine operations by transferring them into a visual mode and offering all-rounded CLI-powered automation. 

One of the notable tools is the Database Designer, with its subset, Table Designer. Here’s how they work: 

• With Database Designer, you can create and edit databases, visualizing all objects and their relationships on ER diagrams and utilizing reverse and forward engineering.
• Table Designer is a feature tailored for creating and modifying table structures, including columns, indexes, triggers, primary and foreign keys, and more.  

These tools emphasize modeling, freeing you from manual coding, as the dbForge Studio for SQL Server automatically generates the necessary SQL code for databases and tables. Here’s how:

To work with primary keys using the visualization capacities of the Table Designer features, you can use both the CREATE TABLE and ALTER TABLE commands.  

1. Right-click the Tables node in the database and select New Table.
2. Establish a primary key on one or several columns in the Table Designer window.

Table Designer has an embedded SQL Editor that showcases the CREATE TABLE statement and adjusts it in SQL with every change you introduce in a visual format.

On the Constraints tab of Table Designer, you can view the primary key properties.

To work with a primary key in the existing table, right-click that table and select Edit Table. In the Table Designer window, you can drop the existing primary key by unchecking it.

After completing the task, click Apply Changes to save them for the current table. Also, you can save the SQL scripts generated by dbForge Studio for SQL Server for further reference.

Conclusion

Primary keys play a crucial role in SQL Server databases, serving as unique identifiers for each record within a specific table. This way, they uphold data integrity and consistency throughout the entire database. Understanding primary keys is thus a fundamental skill for any database specialist.

Modern professional tools, such as dbForge Studio for SQL Server, allow you to handle primary keys effortlessly, in one click, and also perform countless other database-related tasks. If you’re looking for a dependable solution to simplify and streamline your daily workflows, with a robust functionality and user-friendly interface, consider giving dbForge Studio a try. It offers a fully functional 30-day free trial to thoroughly evaluate all of its features under real-world conditions.