Unique index behavior in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a unique index in a database?

A unique index ensures that all values in the indexed column(s) are different. It prevents duplicate values 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 contain NULL values. A unique index allows 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 prevents duplicate values.

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 each row.

beginner

Does a unique index improve query performance? How?

Yes, a unique index helps the database find rows faster when searching by the indexed column(s), because it organizes data for quick lookups.

What does a unique index prevent in a database table?

A Duplicate values in the indexed column(s)

B NULL values in any column

C All rows from being deleted

D Multiple tables from being joined

A unique index prevents duplicate values in the indexed column(s), ensuring each value is unique.

Which of the following is true about a unique index?

A It can be created on multiple columns

B It allows duplicate values

C It automatically creates a primary key

D It disables data insertion

A unique index can be created on multiple columns to enforce uniqueness on the combination of those columns.

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

A Syntax error

B Duplicate key error

C Connection error

D Timeout error

The database returns a duplicate key error because the unique index forbids duplicate values.

Does a unique index allow NULL values by default?

A Only if the column is a primary key

B No, it never allows NULLs

C Only in MySQL

D Yes, usually it allows NULLs

Unique indexes usually allow NULL values unless the column is defined as NOT NULL.

How does a unique index affect query speed?

A Slows down queries

B Has no effect

C Speeds up searches on indexed columns

D Prevents queries from running

A unique index speeds up searches by organizing data for quick lookups on the indexed columns.

Explain what a unique index is and how it helps maintain data integrity.

Think about how a unique index stops repeated values in a column.

Describe the difference between a unique index and a primary key.

Consider NULL values and row identification.