<< SQL Server Hosting - HostForLIFE :: SQL Database Backup and Restore Procedure | SQL Server Hosting - HostForLIFE :: Getting Started with MSSQL and ASP.NET Core with Docker-Compose >>

SQL Server Hosting - HostForLIFE :: Primary Key and Unique Keys

July 1, 2024 07:01 by author

Peter

In Database Management Systems, both Primary Key and Unique key constraints play crucial roles in preserving sharpness of the Data stored. Both make sure of distinctness across a column or group(columns) with some differences.

Primary Key Constraints

Uniqueness: A primary key constraint enforces that all values in the designated primary key column be unique. Primary key constraint can only have one per table.
Not Null : A primary key column will not accept NULL value. Every row must have a non empty primary key value.
Single Column or Composite: The primary key can be made of a single column or more than one (composite primary key).
Default Indexing: A clustered index will be created on the columns that define a primary key by default. The clustered index is also stored on a b-tree and it sorts the table data according to the primary key, increasing query performance when joining witht he same keys as in this case.
It will provide a way to maintain the relationships between tables - The primary key of one table can be referenced as a foreign key in another table.
Unique Primary Key: A table must have one primary key constraint, and it can be a combination of more than 1 column.

Unique Key Constraints

Uniqueness: Uniqieness Key constraint also makes sure that all the values in Unique key columns are unique. Even though, you can have multiple unique constraints for a table.
Nullable: Unique key columns can contain NULL s, although each NULL is unique in this case unlike primary keys cells.
Single Column or Composite: As for the primary key, unique keys could also be defined in a single column manner or composite.
Index :An index itself in non-clustered type and is usually created on unique key column. The unique key index is designed to accelerate searches or filters based on the unique key.
Foreign Keys: Unique keys can also be referenced as foreign key in other tables.
Multiple Unique Keys: A table may have more than one unique key constraint

Primary KeyUse a primary keywhenever you want to ensure every row in the table has its own unique identifier. Usually the main entity which is represented in a table

Unique key is used when you want to ensure unique values for a column or set of columns and that value(s) doesn't form main identifier in the table. You can also have multiple unique keys thus enforcing the uniqueness on different columns combinations.

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.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.