SQL Primary Key vs Unique Key — Understanding Uniqueness in Tables

The school uses roll number to identify a student and also wants an optional email address to be unique when supplied.

A primary key identifies each table row and cannot contain NULL. A unique constraint prevents duplicate permitted key values, with NULL rules that depend on the engine.

A table has one primary-key constraint, which may use several columns, and can have several unique constraints. A foreign key can reference an eligible unique parent key according to the engine’s rules. Choosing the primary key is a data-model decision, not a guarantee that every other field is unique.

Related concepts: Data Constraints and Normalize Data.

A Primary Key uniquely identifies each record in a table. It has two strict rules:

• It must be unique across all rows
• It cannot be NULL

A Unique Key also enforces uniqueness, but:

• It can accept NULL values
• You can have multiple unique keys in a table

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  aadhaar_number BIGINT UNIQUE,
  email VARCHAR(100) UNIQUE
);

Explanation:

• roll_no is the Primary Key — required and must be unique
• aadhaar_number and email are Unique Keys — can be NULL if not provided

SQL · portable concepts; preserved examples identify their engine

-- Valid
INSERT INTO students VALUES (1, 'Aarav Sharma', 123456789012, 'aarav@example.com');

-- Valid even if Aadhaar is missing
INSERT INTO students VALUES (2, 'Diya Iyer', NULL, 'diya@example.com');

-- Invalid: duplicate roll_no (Primary Key violation)
INSERT INTO students VALUES (1, 'Mehul Agarwal', 222233334444, 'mehul@example.com');

-- Invalid: duplicate email (Unique Key violation)
INSERT INTO students VALUES (3, 'Sneha Patil', NULL, 'aarav@example.com');

SQL · portable concepts; preserved examples identify their engine

• roll_no – Primary Key (must exist for every student)
• aadhaar_number – Unique Key (may be missing initially)
• email – Unique Key (optional but must not repeat)

• Always define a Primary Key for every table
• Use Unique Keys for business-critical identifiers (like email, phone)
• Do not overload Primary Key with business logic (keep it technical)
• Avoid NULLs in Unique Keys unless truly optional

MySQL commonly allows multiple NULL values under a unique constraint, while SQL Server and PostgreSQL options differ. Do not claim that every unique key permits exactly one NULL or that a primary key must be one column.

Show how repeated class values remain valid even though roll numbers identify individual rows.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT class, COUNT(*) AS students FROM practice_students GROUP BY class ORDER BY class;

Class is intentionally shared by multiple students. Uniqueness belongs to the student identity, or another field whose business rule truly requires it.

A primary key identifies each table row and cannot contain NULL. A unique constraint prevents duplicate permitted key values, with NULL rules that depend on the engine.

Class is intentionally shared by multiple students. Uniqueness belongs to the student identity, or another field whose business rule truly requires it.

Continue your understanding with Data Constraints and Normalize Data.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.