Primary keys and uniqueness in SQL

Introduction

Primary keys help us identify each row in a table uniquely. Uniqueness ensures no two rows have the same key, so we can find data easily and avoid confusion.

When creating a table to store user information and you want each user to have a unique ID.

When you want to make sure no two products in your store have the same product code.

When you need to link orders to customers without mixing them up.

When you want to prevent duplicate entries in a list of emails.

When you want to quickly find a specific record without searching the whole table.

Syntax

SQL

CREATE TABLE table_name (
  column_name datatype PRIMARY KEY,
  other_column datatype,
  ...
);

-- Or add UNIQUE constraint
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

The PRIMARY KEY column must have unique values and cannot be NULL.

The UNIQUE constraint also enforces uniqueness but allows NULL values.

Examples

This creates a Users table where UserID is the primary key, so each user has a unique ID.

SQL

CREATE TABLE Users (
  UserID INT PRIMARY KEY,
  UserName VARCHAR(50)
);

This creates a Products table where ProductCode must be unique but is not the primary key.

SQL

CREATE TABLE Products (
  ProductCode VARCHAR(20) UNIQUE,
  ProductName VARCHAR(100)
);

This adds a uniqueness rule to the Email column in the Employees table to prevent duplicate emails.

SQL

ALTER TABLE Employees
ADD CONSTRAINT unique_email UNIQUE (Email);

Sample Program

This example creates a Customers table with CustomerID as the primary key and Email as a unique column. It inserts two customers successfully. Attempts to insert duplicate CustomerID or Email will fail.

SQL

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Email VARCHAR(100) UNIQUE,
  Name VARCHAR(100)
);

INSERT INTO Customers (CustomerID, Email, Name) VALUES (1, '[e-mail]', 'Alice');
INSERT INTO Customers (CustomerID, Email, Name) VALUES (2, '[e-mail]', 'Bob');

-- This will fail because CustomerID 1 already exists
-- INSERT INTO Customers (CustomerID, Email, Name) VALUES (1, '[e-mail]', 'Charlie');

-- This will fail because Email '[e-mail]' already exists
-- INSERT INTO Customers (CustomerID, Email, Name) VALUES (3, '[e-mail]', 'Alice2');

SELECT * FROM Customers;

Important Notes

Primary key columns are automatically unique and cannot be NULL.

Unique constraints allow NULL values.

Trying to insert duplicate values in primary key or unique columns causes errors.

Use primary keys to identify rows and unique constraints to prevent duplicates in other important columns.

Summary

Primary keys uniquely identify each row and cannot be NULL.

Unique constraints ensure no duplicate values in a column but allow NULL values.

Both help keep data clean and easy to find.