The SQL key concept refers to a specific field or a combination of fields in a database table. Keys improve data retrieval speed and also help establish relationships between tables.
A primary key is a unique identifier for each record within a table that prevents duplicate values and allows for efficient retrieval and manipulation of data in the table. As such, a primary key is one of the fundamental elements of database design.
In this article, we’ll explore the specifics of the primary key in SQL Server, discussing its types and how it’s used.
Contents
- Primary key: definition and constraints
- Creating a primary key in SQL Server
- Primary key vs. unique key
- How to create and manage primary keys with dbForge Studio for SQL Server
- Conclusion
Primary key: definition and constraints
The primary key in SQL Server is a column or a combination of columns in a particular database table that serves as a unique identifier for each row in that table and creates a unique index for the table data.
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.
Creating a primary key in SQL Server
There are two ways to create a primary key:
- At the time of creation of the table using the CREATE TABLE command
- After the creation of the table using the ALTER TABLE command
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 has any data stored in it, you need to make sure that the column you want to promote as the primary key contains unique values (and NOT NULL) before adding that constraint. Otherwise, the command will result in an error.
How to create a single primary key using the CREATE TABLE statement
The below example demonstrates creating a table with a primary key.
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
);
Now, the HumanResources.Training table has the Employee_ID primary key column, meaning it must contain unique values only.
Typically, we define the primary key for a table during its initial creation. However, situations may arise where a table is created without a primary key. In such cases, it’s possible to introduce a primary key using the ALTER TABLE command.
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 below command:
ALTER TABLE HumanResources.Courses
ADD CONSTRAINT PK_Courses PRIMARY KEY(Employee_ID);
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.
Create a composite primary key with the ALTER TABLE statement
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.
ALTER TABLE AdventureWorks2022.HumanResources.Training
ADD CONSTRAINT PK_Training PRIMARY KEY CLUSTERED (Employee_ID, Last_Name, Course_Title);
We have established a composite primary key on an existing table.
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 primary key.
How to drop an existing primary key
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.
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.
How to create and manage primary keys with dbForge Studio for SQL Server
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. Yet, dbForge Studio 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.
- 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 Studio automatically generates the necessary SQL code for databases and tables.
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.
- Right-click the Tables node in the database and select New Table.
- 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.
