SQL · Theory

Unique Index

← All stacks

Theory

80/439

Unique Index

If you can teach Unique Index using lookup by roll, you know it. If you only know the heading, you don’t.

For Unique Index, an index is in play. Don’t blame it until you have traced lookup by roll.

Skip Unique Index and indexing a low-cardinality flag only shows up in a big table.

Unique Index shows up in a big table. Name lookup by roll, not “a real-world scenario”.

If lookup by roll breaks under Unique Index, check indexing a low-cardinality flag only first.

Unique Index is not a lonely heading. Meera ties it to lookup by roll.

Viva for Unique Index — what it is → PRIMARY KEY(roll) + INDEX → the mistake (indexing a low-cardinality flag only).

Exam tip

Say Unique Index in one breath, then PRIMARY KEY(roll) + INDEX, then indexing a low-cardinality flag only.

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 — Meera uses it for lookup by roll in a big table.
RuleUnique Index → PRIMARY KEY(roll) + INDEX.
RememberUnique Index + an index (a big table).
UseUnique Index in a big table (lookup by roll).
TrapUnique Index — indexing a low-cardinality flag only.
ExUnique Index → lookup by roll.

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