Primary Key and Unique Key Constraints

Primary Key Constraints and Unique Key Constraints are both important in database management systems to maintain data integrity and ensure the accuracy of the data stored. They both ensure uniqueness within a column or set of columns but with some key differences.

Primary Key Constraints

Uniqueness: A primary key constraint ensures that all values in the primary key column are unique. A table can only have one primary key constraint.
Not Null: A primary key column cannot contain NULL values. Each row must have a valid value for the primary key.
Single Column or Composite: A primary key can consist of a single column or multiple columns (composite primary key).
Default Indexing: A clustered index is automatically created on the primary key columns. This index physically organizes the table data based on the primary key, improving performance for queries that involve the primary key.
Foreign Keys: The primary key of one table can be referenced as a foreign key in another table to establish relationships between tables.
Only One Primary Key: A table can have only one primary key constraint, though it can be composed of multiple columns.

Unique Key Constraints

Uniqueness: A unique key constraint also ensures that all values in the unique key columns are unique. However, a table can have multiple unique constraints.
Nullable: Unlike primary key columns, unique key columns can contain NULL values, but each NULL value is considered unique.
Single Column or Composite: Similar to primary keys, unique keys can be defined on a single column or multiple columns (composite unique key).
Index: A non-clustered index is typically created on a unique key column. This index helps speed up queries that involve searching or filtering based on the unique key.
Foreign Keys: Unique keys can also be referenced as foreign keys in other tables.
Multiple Unique Keys: A table can have multiple unique key constraints.

Use a primary key when you need a guaranteed unique identifier for each row in the table. This is often the case for the main entity represented by the table.

Use a unique key when you need to ensure a column or set of columns has unique values, but is separate from the main identifier of the table. You can have multiple unique keys to enforce uniqueness on different sets of columns.

Example

User Table

Primary Key: user_Id (guaranteed unique identifier for each user).
Unique Key: user_Email (ensures no duplicate email addresses).

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    user_email VARCHAR(255) UNIQUE,
    user_name VARCHAR(50) UNIQUE,
    user_fullName VARCHAR(100)
);
-- user_id is the Primary Key
-- user_email and user_name are the Unique Keys

Examples

Primary Key Table Structure.

-- First SQL Statement
CREATE TABLE tbl_students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE
);

-- Second SQL Statement
CREATE TABLE [tbl_students](
    [student_id] [int] NOT NULL,
      NULL,
      NULL,
    [date_of_birth] [date] NULL,
    PRIMARY KEY CLUSTERED
    (
        [student_id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Primary Key and Unique Key Table Structure.

CREATE TABLE tbl_employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    phone_number VARCHAR(20) UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

employee_id is the primary key and email, and phone_number is the unique key.

CREATE TABLE [tbl_employees](
    [employee_id] [int] NOT NULL,
      NULL,
      NULL,
      NULL,
      NULL,
    PRIMARY KEY CLUSTERED
    (
        [employee_id] ASC
    ) WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON, 
        OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY],
    UNIQUE NONCLUSTERED
    (
        [phone_number] ASC
    ) WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON, 
        OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY],
    UNIQUE NONCLUSTERED
    (
        [email] ASC
    ) WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON, 
        OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

The primary key has a clustered index and the unique key has non clustered index.

Composite Keys

It is used as a primary key or a unique key, involving combining multiple columns to uniquely identify a row in a database table.

Example

Composite Primary Key: A primary key can consist of a single column or multiple columns.

CREATE TABLE tbl_enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

student_id, and course_id are used to create composite primary key.

CREATE TABLE [tbl_enrollments](
    [student_id] [int] NOT NULL,
    [course_id] [int] NOT NULL,
    [enrollment_date] [date] NULL,
    PRIMARY KEY CLUSTERED
    (
        [student_id] ASC,
        [course_id] ASC
    ) WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON, 
        OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

Composite Unique Key: A table can have multiple unique key constraints.

CREATE TABLE tbl_orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    order_date DATE,
    UNIQUE (product_id, customer_id)
);

product_id and customer_id are used to create a composite unique key.

CREATE TABLE [tbl_orders](
    [order_id] [int] NOT NULL,
    [product_id] [int] NULL,
    [customer_id] [int] NULL,
    [order_date] [date] NULL,
    PRIMARY KEY CLUSTERED
    (
        [order_id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
    UNIQUE NONCLUSTERED
    (
        [product_id] ASC,
        [customer_id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

The query file is attached.