Primary Key in SQL Server

Primary Key in SQL Server

1. Introduction to Primary Key:
A Primary Key is a fundamental concept in SQL Server and other database systems. It is a unique identifier for each record in a table, ensuring that no two rows have the same key value. A primary key is used to enforce data integrity, establish relationships between tables, and optimize database performance.

2. Creating a Primary Key:
In SQL Server, you can create a primary key using the PRIMARY KEY constraint within the CREATE TABLE statement or by altering an existing table to add a primary key constraint.

Example: Creating a Primary Key during Table Creation

sql
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(100)
);

Explanation:
In this example, we create a table named "Employees" with four columns. The column "EmployeeID" is designated as the primary key using the PRIMARY KEY constraint. It means that each "EmployeeID" value must be unique for every row in the "Employees" table.

Example: Adding a Primary Key to an Existing Table

sql
ALTER TABLE Employees
ADD PRIMARY KEY (EmployeeID);

Explanation:
In this example, we alter the "Employees" table to add a primary key constraint on the "EmployeeID" column. This ensures that the "EmployeeID" becomes the unique identifier for each row.

3. Characteristics of Primary Key:

• A primary key must contain unique values for each row in the table.
• The primary key column cannot contain NULL values because it must uniquely identify each record.
• A table can have only one primary key.
• Primary keys are automatically indexed, which can improve query performance.

4. Auto Incrementing Primary Key:
Often, a primary key is designed to be auto-incrementing, meaning the database generates a new unique value for the primary key whenever a new row is inserted.

Example: Creating an Auto-Incrementing Primary Key

sql
CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(100),
    Price DECIMAL(10,2)
);

Explanation:
In this example, the "ProductID" column is designated as the primary key and set to auto-increment using the IDENTITY(1,1) property. The first value (1) is the seed value, and the second value (1) is the increment value. This means that every time a new row is inserted into the "Products" table, the "ProductID" will automatically get a unique value, starting from 1 and incrementing by 1 for each new row.

5. Dropping a Primary Key:
To remove a primary key constraint from a column, you can use the ALTER TABLE statement.

Example: Removing a Primary Key

sql
ALTER TABLE Employees
DROP CONSTRAINT PK_Employees;

Explanation:
In this example, the primary key constraint named "PK_Employees" is removed from the "Employees" table.

Conclusion:
The primary key is a crucial component of a well-designed database schema. It ensures data integrity, uniqueness, and helps in establishing relationships between tables. When used correctly, it can significantly enhance the efficiency and reliability of your SQL Server database.