UNIQUE Constraint & Candidate Keys0%

UNIQUE Constraint & Candidate Keys

Beginner12 min read•Updated: 2026-09-12

UNIQUE Constraint & Candidate Keys: Guaranteeing Distinctness

While a table can have only one Primary Key, real-world entities frequently possess multiple attributes that must be unique across the entire database. For example, in a user registration system, every user has an auto-incremented user_id (Primary Key), but their email address, username, and phone number must also be strictly unique. This is enforced using UNIQUE Constraints.

1. What is a UNIQUE Constraint?

A UNIQUE constraint guarantees that all values in a column (or group of columns) are distinct. If an INSERT or UPDATE statement attempts to store a duplicate value, MySQL halts the transaction with error 1062 (Duplicate entry).

1CREATE TABLE registered_users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL,
  phone_number VARCHAR(20),
  
  -- Table-level UNIQUE constraint with custom name:
  CONSTRAINT uq_users_email UNIQUE (email),
  CONSTRAINT uq_users_phone UNIQUE (phone_number)
10);

2. Primary Key vs UNIQUE Constraint

Feature	Primary Key	UNIQUE Constraint
Quantity Allowed	Strictly 1 per table.	Multiple allowed per table.
Allows NULL?	Never (`NOT NULL` is strictly enforced).	Yes! (A UNIQUE column can contain `NULL` unless explicitly declared `NOT NULL`).
Index Created	Automatically creates a Clustered Index in InnoDB.	Automatically creates a Secondary Unique B-Tree Index.
Purpose	Primary row identifier for table organization and foreign keys.	Guarantees business uniqueness across secondary attributes.

The `NULL` Exception in Unique Constraints

In MySQL InnoDB, multiple rows can store NULL in a UNIQUE column without violating the constraint! Because each NULL mathematically represents an unknown value, one unknown is never considered equal to another unknown.

1-- In registered_users, phone_number has a UNIQUE constraint.
2-- Both inserts succeed:
3INSERT INTO registered_users (username, email, phone_number) VALUES ('user1', 'u1@test.com', NULL);
4INSERT INTO registered_users (username, email, phone_number) VALUES ('user2', 'u2@test.com', NULL);

3. What are Candidate Keys?

In relational database theory, any column (or set of columns) capable of uniquely identifying a row is called a Candidate Key:

One candidate key is chosen by the architect as the Primary Key.
All remaining candidate keys are enforced as Alternate Keys using UNIQUE constraints.

4. Composite UNIQUE Constraints

A Composite UNIQUE Constraint enforces uniqueness across the combination of multiple columns:

1-- Course Enrollments: A student can enroll in multiple courses,
2-- and a course has multiple students, but a student cannot enroll in 
3-- the SAME course twice!
4CREATE TABLE enrollments (
5    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
6    student_id INT NOT NULL,
7    course_id INT NOT NULL,
8    enrolled_date DATE NOT NULL,
9    
10    CONSTRAINT uq_student_course UNIQUE (student_id, course_id)
11);

5. Adding and Dropping UNIQUE Constraints

1-- Add unique constraint to existing table:
2ALTER TABLE registered_users
3ADD CONSTRAINT uq_user_tax_id UNIQUE (tax_id);
4 
5-- Drop a unique constraint (In MySQL, you drop the underlying index):
6ALTER TABLE registered_users
7DROP INDEX uq_user_tax_id;

6. Best Practices & Common Pitfalls

Always Name Your Constraints: Always write CONSTRAINT uq_table_column UNIQUE (col) rather than inline UNIQUE. Naming your constraints makes debugging production error logs and dropping constraints via migrations straightforward.
Automatic Index Creation: Remember that every UNIQUE constraint creates an underlying B-Tree index. While this speeds up lookups, unnecessary unique indexes add write overhead on INSERT and UPDATE operations.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How many Primary Keys vs UNIQUE constraints can a single MySQL table possess?

Question 2

What happens in MySQL when multiple rows store NULL in a column with a UNIQUE constraint (assuming it is not marked NOT NULL)?

Question 3

What underlying database structure does MySQL automatically generate when a UNIQUE constraint is declared?

Question 4

How do you remove a UNIQUE constraint named uq_email from a table named users in MySQL?

Question 5

In relational database theory, what is a column called if it is eligible to be a primary key but was not chosen?

Next Lesson

CHECK Constraints in MySQL

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
NOT NULL and DEFAULT Constraints	CHECK Constraints in MySQL

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

CHECK Constraints in MySQL

UNIQUE Constraint & Candidate Keys

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

UNIQUE Constraint & Candidate Keys: Guaranteeing Distinctness

1. What is a UNIQUE Constraint?

1CREATE TABLE registered_users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL,
  phone_number VARCHAR(20),
  
  -- Table-level UNIQUE constraint with custom name:
  CONSTRAINT uq_users_email UNIQUE (email),
  CONSTRAINT uq_users_phone UNIQUE (phone_number)
10);

2. Primary Key vs UNIQUE Constraint

Feature	Primary Key	UNIQUE Constraint
Quantity Allowed	Strictly 1 per table.	Multiple allowed per table.
Allows NULL?	Never (`NOT NULL` is strictly enforced).	Yes! (A UNIQUE column can contain `NULL` unless explicitly declared `NOT NULL`).
Index Created	Automatically creates a Clustered Index in InnoDB.	Automatically creates a Secondary Unique B-Tree Index.
Purpose	Primary row identifier for table organization and foreign keys.	Guarantees business uniqueness across secondary attributes.

The `NULL` Exception in Unique Constraints

1-- In registered_users, phone_number has a UNIQUE constraint.
2-- Both inserts succeed:
3INSERT INTO registered_users (username, email, phone_number) VALUES ('user1', 'u1@test.com', NULL);
4INSERT INTO registered_users (username, email, phone_number) VALUES ('user2', 'u2@test.com', NULL);

3. What are Candidate Keys?

In relational database theory, any column (or set of columns) capable of uniquely identifying a row is called a Candidate Key:

One candidate key is chosen by the architect as the Primary Key.
All remaining candidate keys are enforced as Alternate Keys using UNIQUE constraints.

4. Composite UNIQUE Constraints

A Composite UNIQUE Constraint enforces uniqueness across the combination of multiple columns:

1-- Course Enrollments: A student can enroll in multiple courses,
2-- and a course has multiple students, but a student cannot enroll in 
3-- the SAME course twice!
4CREATE TABLE enrollments (
5    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
6    student_id INT NOT NULL,
7    course_id INT NOT NULL,
8    enrolled_date DATE NOT NULL,
9    
10    CONSTRAINT uq_student_course UNIQUE (student_id, course_id)
11);

5. Adding and Dropping UNIQUE Constraints

1-- Add unique constraint to existing table:
2ALTER TABLE registered_users
3ADD CONSTRAINT uq_user_tax_id UNIQUE (tax_id);
4 
5-- Drop a unique constraint (In MySQL, you drop the underlying index):
6ALTER TABLE registered_users
7DROP INDEX uq_user_tax_id;

6. Best Practices & Common Pitfalls

Always Name Your Constraints: Always write CONSTRAINT uq_table_column UNIQUE (col) rather than inline UNIQUE. Naming your constraints makes debugging production error logs and dropping constraints via migrations straightforward.
Automatic Index Creation: Remember that every UNIQUE constraint creates an underlying B-Tree index. While this speeds up lookups, unnecessary unique indexes add write overhead on INSERT and UPDATE operations.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How many Primary Keys vs UNIQUE constraints can a single MySQL table possess?

Question 2

What happens in MySQL when multiple rows store NULL in a column with a UNIQUE constraint (assuming it is not marked NOT NULL)?

Question 3

What underlying database structure does MySQL automatically generate when a UNIQUE constraint is declared?

Question 4

How do you remove a UNIQUE constraint named uq_email from a table named users in MySQL?

Question 5

In relational database theory, what is a column called if it is eligible to be a primary key but was not chosen?

Next Lesson

CHECK Constraints in MySQL

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
NOT NULL and DEFAULT Constraints	CHECK Constraints in MySQL

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

UNIQUE Constraint & Candidate Keys: Guaranteeing Distinctness

1. What is a UNIQUE Constraint?

2. Primary Key vs UNIQUE Constraint

The NULL Exception in Unique Constraints

3. What are Candidate Keys?

4. Composite UNIQUE Constraints

5. Adding and Dropping UNIQUE Constraints

6. Best Practices & Common Pitfalls

Multiple Choice Questions

CHECK Constraints in MySQL

Related Lessons

Practice Quiz

Loading Page Content...

UNIQUE Constraint & Candidate Keys: Guaranteeing Distinctness

1. What is a UNIQUE Constraint?

2. Primary Key vs UNIQUE Constraint

The NULL Exception in Unique Constraints

3. What are Candidate Keys?

4. Composite UNIQUE Constraints

5. Adding and Dropping UNIQUE Constraints

6. Best Practices & Common Pitfalls

Multiple Choice Questions

CHECK Constraints in MySQL

Related Lessons

Practice Quiz

The `NULL` Exception in Unique Constraints

The `NULL` Exception in Unique Constraints