Unique indexes in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a unique index in a database?

A unique index is a database feature that ensures all values in the indexed column(s) are different. It prevents duplicate entries in those columns.

intermediate

How does a unique index differ from a primary key?

A primary key is a unique index that also uniquely identifies each row and cannot be NULL. A unique index allows unique values but can have NULLs unless specified otherwise.

beginner

What happens if you try to insert a duplicate value into a column with a unique index?

The database will reject the insert and return an error because the unique index enforces uniqueness and does not allow duplicates.

beginner

How do you create a unique index on a column in MySQL?

You use the SQL command: <br>CREATE UNIQUE INDEX index_name ON table_name(column_name);

intermediate

Can a unique index be created on multiple columns? What does it enforce?

Yes, a unique index can cover multiple columns. It enforces that the combination of values across those columns is unique for every row.

What does a unique index guarantee in a database table?

A Automatically creates a primary key

B Faster data retrieval only

C Allows duplicate values but sorts them

D No duplicate values in the indexed column(s)

A unique index ensures that no two rows have the same value in the indexed column(s).

Which SQL command creates a unique index on the 'email' column of the 'users' table?

A CREATE UNIQUE INDEX idx_email ON users(email);

B CREATE INDEX idx_email ON users(email);

C ALTER TABLE users ADD UNIQUE email;

D CREATE PRIMARY KEY ON users(email);

The correct syntax to create a unique index is CREATE UNIQUE INDEX with the index name and table.column.

Can a unique index allow NULL values in MySQL?

A Only one NULL is allowed

B No, NULLs are not allowed

C Yes, multiple NULLs are allowed

D NULLs are converted to empty strings

In MySQL, unique indexes allow multiple NULL values because NULL is not considered equal to another NULL.

What error occurs if you insert a duplicate value into a unique indexed column?

A Syntax error

B Duplicate entry error

C Timeout error

D No error, duplicates allowed

The database returns a duplicate entry error to prevent violating the unique index constraint.

What is a composite unique index?

A A unique index on multiple columns combined

B A unique index on a single column

C An index that is not unique

D A primary key index

A composite unique index ensures the combination of values across multiple columns is unique.

Explain what a unique index is and why it is useful in a database.

Think about how unique indexes help keep data clean and organized.

Describe how to create a unique index in MySQL and what happens if you try to insert duplicate data.

Recall the SQL command and the database behavior on duplicates.