Difference between Primary and Unique Key in SQL Server

A primary key is a column or set of columns in a table that uniquely identifies each row in the table. A primary key cannot contain null values and must have a unique value for each row. It is used to enforce the integrity of the data and to create a link between tables in a relational database.

A unique key is also a column or set of columns in a table that uniquely identifies each row in the table, similar to a primary key. However, a unique key can contain null values and must have a unique value for each row.

For example, consider a table called “Employees” which contains the following columns: EmployeeID, EmployeeName, and Department. The EmployeeID column can be set as the primary key because it is unique for each employee and cannot contain a null value. The EmployeeName column can be set as a unique key because it is also unique for each employee but can contain null values.

In summary, a primary key is a unique key that cannot contain null values, while a unique key is a unique key that can contain null values.

Here’s an example of creating a table called “Employees” with a primary key and a unique key in T-SQL:

CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(50) NOT NULL,
    Department NVARCHAR(50) NOT NULL,
    UNIQUE (EmployeeName)
);

In this example, the “EmployeeID” column is set as the primary key and the “EmployeeName” column is set as a unique key. The “NOT NULL” constraint ensures that these columns cannot contain null values.

You can also create a primary key with multiple columns like this:

CREATE TABLE Employees
(
    EmployeeID INT,
    EmployeeName NVARCHAR(50) NOT NULL,
    Department NVARCHAR(50) NOT NULL,
    UNIQUE (EmployeeName),
    PRIMARY KEY (EmployeeID, Department)
);

In this example, the primary key is set to the combination of EmployeeID and Department columns, which means that the combination of those two columns must be unique in the table.

It’s important to note that, in SQL Server, you can have only one primary key per table, but you can have multiple unique keys.