SQL · Theory

Unique Index

Theory

80/439

Unique Index

CREATE UNIQUE INDEX idx_email ON students(email); is UNIQUE constraint + index. Inserting the same email twice fails. Search by email is fast.

Same idea as UNIQUE KEY in CREATE TABLE. Pick one style.

Trap — UNIQUE index on a column that can legally repeat.

On the example next to this theory — Unique Index — keys and constraints protect data integrity; indexes speed lookups.

Exam tip

CREATE UNIQUE INDEX idx_email ON students(email);

Unique Index — sample query

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);
INSERT INTO authors VALUES (1, 'a@x.com');
INSERT INTO books VALUES (1, 1, 'SQL Basics');
SELECT * FROM books;

Unique Index — keys and constraints protect data integrity; indexes speed lookups.

Short notes

DefUNIQUE INDEX = no duplicates + fast lookup.
RuleEmail/username are typical unique indexes.
TrapUnique on city.

CREATE TABLE authors ( id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL ); CREATE TABLE books ( id INTEGER PRIMARY KEY, author_id INTEGER NOT NULL, title TEXT NOT NULL, FOREIGN KEY (author_id) REFERENCES authors(id) ); INSERT INTO authors VALUES (1, 'a@x.com'); INSERT INTO books VALUES (1, 1, 'SQL Basics'); SELECT * FROM books;

Unique Index

Unique Index — sample query

Short notes

Questions

Unique Index

Unique Index — sample query

Short notes

Questions