SQL Unique Index

Does everything a regular index does, plus rejects any row that would duplicate an existing value.

What & Why

A unique index both speeds up lookups on a column AND enforces that no two rows can share the same value in it. Every PRIMARY KEY is backed by a unique index automatically — this lesson is about building one explicitly on a different column.

See How It Works

BUSINESS QUESTION

Protect the business rule that product feature names are unique regardless of letter case.

id	name	description	team	released_at	deprecated_at
1	Activation Checklist	Guides new users through setup	growth	2024-01-20	NULL
2	CSV Export	Exports report data	platform	2024-02-14	NULL
3	Invite Nudges	Prompts workspace collaboration	growth	2024-03-05	NULL
4	Legacy Dashboard	Original reporting surface	analytics	2023-08-10	2024-06-01

EXAMPLE QUERY

SELECT
  LOWER(name) AS normalized_feature_name,
  COUNT(*) AS occurrences
FROM product.features
GROUP BY LOWER(name)
HAVING COUNT(*) > 1
ORDER BY normalized_feature_name;

RESULT — no duplicate normalized feature names

normalized_feature_name	occurrences

Every feature name remains unique after LOWER normalization, so HAVING returns no rows.

Advanced business practice