Overview: What Is A Primary Key
In SQL Server, a primary key is a column or a set of multiple columns, that uniquely identifies each row (record) in a table. Here’s a more detailed explanation:
Uniqueness: A primary key ensures that each value in the designated column(s) is unique within the table. This uniqueness constraint prevents duplicate rows from being inserted into multiple records within the table.
Non-nullability: By default, a primary key column cannot contain NULL values. This ensures that every row in two columns of the table has a valid identifier.
Indexed: SQL Server automatically creates a unique clustered index or non-clustered index on the primary key column(s). This index facilitates fast data retrieval, as it organizes the data in the table based on the primary key values.
Constraints: A primary key constraint is applied to the primary key column(s) to enforce the uniqueness and non-nullability rules. This constraint prevents the modification or deletion only one column of key values that are referenced by foreign keys in other tables, ensuring data integrity and referential integrity.
Creation: You can define a primary key constraint when creating a table using the PRIMARY KEY constraint syntax, or you can add it to an already existing table by using an ALTER TABLE statement.
Here’s an example of creating more than one column primary key in a table with a primary key in SQL Server:
Example
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT
);
In this example, the employee_id column is designated as the primary key. It uniquely identifies each employee in the employees table.
In SQL Server, there are several types of primary keys that can be used to uniquely identify rows in a table. The most common types include:
Single-Column Primary Key:
A single column is designated as the primary key.
This is the simplest form of primary key and is suitable when one column uniquely identifies each row in the table.
Example: employee_id INT PRIMARY KEY
Composite Primary Key:
Two or more columns together form the primary key.
This is used when no single column uniquely identifies each row, but a combination of columns does.
Example: (employee_id INT, department_id INT) PRIMARY KEY
Surrogate Key:
A synthetic key created specifically for use as the primary key.
It does not have any inherent meaning and is typically an auto-incremented or generated value.
Example: id INT PRIMARY KEY IDENTITY
Natural Key:
A column or set of columns with values that have inherent meaning and can uniquely identify each row.
Examples include social security numbers, email addresses, or product codes.
Natural keys should be chosen carefully to ensure uniqueness and stability over time.
Example: social_security_number VARCHAR(11) PRIMARY KEY
Alternate Key:
A candidate key that is not chosen as the primary key.
While it is unique, it is not designated as the primary means of identifying rows in the table.
Example: email_address VARCHAR(100) UNIQUE
Non-Clustered Primary Key:
In SQL Server, by default, the primary key is enforced by a clustered index, which physically orders the rows on disk based on the key values.
A non-clustered primary key does not specify a physical ordering of rows on disk and is backed by a non-clustered index.
Example: CREATE TABLE example (id INT PRIMARY KEY NONCLUSTERED)
These are the common types of primary keys used in SQL Server. The choice of which type of primary key sql is to use depends on factors such as the data model, the requirements of the application, and performance considerations.
Let’s use the existing employees table from a previous example to demonstrate each type of unique values of primary key:
Single-Column Primary Key:
-- Altering the table to add a primary key ALTER TABLE employees ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (employee_id);Composite Primary Key:
-- Altering the table to add a composite primary key ALTER TABLE employees ADD CONSTRAINT PK_EmployeeDeptID PRIMARY KEY (employee_id, department_id);Surrogate Key:
-- Altering the table to add a surrogate primary key ALTER TABLE employees ADD id INT PRIMARY KEY IDENTITY;Natural Key:
-- Using email address as a natural primary key ALTER TABLE employees ADD CONSTRAINT PK_EmailAddress PRIMARY KEY (email_address);Alternate Key:
-- Adding an alternate key for email address ALTER TABLE employees ADD CONSTRAINT AK_EmailAddress UNIQUE (email_address);Non-Clustered Primary Key:
-- Altering the table to add a non-clustered primary key ALTER TABLE employees ADD CONSTRAINT PK_EmployeeID_NonClustered PRIMARY KEY NONCLUSTERED (employee_id); These examples demonstrate how to alter an existing table to add different types duplicate values of primary keys using T-SQL. You can execute these SQL statements in sequence to apply each type insert duplicate values of primary key to the employees table.
Creating a primary key in SQL Server In Existing Table
To create a primary key in an existing table in SQL Server, you can use the ALTER TABLE statement along with the ADD CONSTRAINT clause. Here’s how you can do it using the alter statement for table:
Suppose you have an existing table named employees and you want to add a new table name with primary key constraint to the employee_id column:
Example:
-- Example table without a primary key
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT
);
-- Add primary key constraint to the existing table
ALTER TABLE employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (employee_id);
In this example:
We have an existing table employees without a primary key constraint.
We use the ALTER TABLE statement to modify the employees table.
The ADD CONSTRAINT clause is used to add null value as a new constraint to the table.
PK_Employees is the name of the primary key constraint.
(employee_id) specifies the column(s) that form the primary key of created table.
After executing this SQL statement, the employee_id column becomes the primary key for the employees table. It will enforce uniqueness and non-nullability for the employee_id column and database table, ensuring data integrity.
Creating a Primary Key in SQL using SQL Server Management Studio
Creating a primary key in SQL Server Management Studio (SSMS) involves using the graphical user interface (GUI) or executing SQL statements in the query editor. Here’s how you can create primary key you can do it using both methods:
Open SSMS: Launch SQL Server Management Studio and connect to your database server.
Navigate to the Table: Expand the Databases node, locate your database, and expand its Tables node. Right-click on the column name the table to which you want to add a primary key and select “Design”.
Design Table: This opens the Table Designer. Right-click on the column(s) you want to include in the table commands the primary key and select “Set Primary Key”.
Save Changes: Save your changes by clicking the “Save” icon in the toolbar or selecting “Save TableName” from the File menu.
How To Drop A Primary Key
To drop a primary key constraint in SQL Server, you can use either SQL statements or the graphical interface in SQL Server Management Studio (SSMS). Here’s following example of how to do it using both methods:
Method 1: Using SQL Statements
You can use the ALTER TABLE statement to drop a primary key constraint in SQL Server. Here’s the syntax:
Example
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Replace table_name with the name of compound key in your table and constraint_name with the name of the primary key constraint you want to drop.
Example:
ALTER TABLE employees
DROP CONSTRAINT PK_EmployeeID;
After executing the SQL statement or making the changes in SSMS, the primary key constraint will be dropped from the table. Make sure to verify the changes after dropping the primary key in new table.