SQL · Theory

Unique Key

Theory

71/439

Unique Key

Unique Key is simple if you keep it on paper. Zara uses it for a Unique Key dry-run in a small live site.

Do Unique Key once by hand. one Unique Key step Zara can write from memory. Change one input. Say the new result out loud.

Skip Unique Key and skipping the failure case for Unique Key shows up in a small live site.

Zara ships Unique Key in a small live site. That is the use case worth saying.

Unique Key trap — skipping the failure case for Unique Key. Zara loses marks for that every viva.

Unique Key is not a lonely heading. Zara ties it to a Unique Key dry-run.

Viva for Unique Key: what it is → one Unique Key step Zara can write from memory → the mistake (skipping the failure case for Unique Key).

Diagram

  students.id  ← PK (unique)
       ▲
       │ FK
  marks.student_id

Exam tip

Say Unique Key in one breath, then one Unique Key step Zara can write from memory, then skipping the failure case for Unique Key.

Unique Key — 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 Key — keys and constraints protect data integrity; indexes speed lookups.

Short notes

DefUnique Key — Zara uses it for a Unique Key dry-run in a small live site.
RuleUnique Key → one Unique Key step Zara can write from memory.
RememberUnique Key + an index (a small live site).
UseUnique Key in a small live site (a Unique Key dry-run).
TrapUnique Key — skipping the failure case for Unique Key.
ExUnique Key → a Unique Key dry-run.

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 Key

Unique Key — sample query

Short notes

Questions

Unique Key

Unique Key — sample query

Short notes

Questions