Primary key vs Unique key in SQL Server
Both primary keys and unique keys in SQL Server are used to enforce the uniqueness of values in a column or a set of columns. However, there are important differences between them:
### Primary Key:
1. **Uniqueness:** A primary key ensures that the values in the specified column or columns are unique across all rows in the table.
2. **Null Values:** A primary key column cannot contain NULL values. Every row must have a unique and non-null value in the primary key column(s).
3. **One per Table:** There can be only one primary key constraint in a table. It can consist of one or multiple columns.
4. **Clustered Index:** In SQL Server, the primary key constraint automatically creates a clustered index on the primary key column(s) if a clustered index does not already exist on the table.
5. **Foreign Key:** Primary key constraints are often used as the target of foreign key constraints in other tables. Foreign keys establish relationships between tables.
**Example of Primary Key:**
```sql
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
Unique Key:
1. **Uniqueness:** Like a primary key, a unique key ensures that the values in the specified column or columns are unique across all rows in the table.
2. **Null Values:** Unlike primary keys, unique key columns can contain NULL values. However, a unique key constraint will allow only one NULL value, meaning that you can have multiple NULLs in the column, but each non-null value must be unique.
3. **Multiple Unique Keys:** Unlike primary keys, you can have multiple unique key constraints in a table. Each unique key can consist of one or multiple columns.
4. **Non-Clustered Index:** Creating a unique key constraint creates a non-clustered index on the unique key column(s) if a clustered index does not already exist on the table.
5. **No Implicit Relationship:** Unique keys are not implicitly used as the target of foreign key constraints.
Example of Unique Key:
CREATE TABLE Customers (
CustomerID INT UNIQUE,
Email VARCHAR(255) UNIQUE,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
In summary, primary keys are used to uniquely identify records in a table and are often used as the target of foreign key constraints, while unique keys provide uniqueness but allow for multiple NULL values and do not establish relationships between tables implicitly. The choice between them depends on the specific requirements of your database schema.
