SQL · Theory

Unique Key

Theory

71/439

Unique Key

UNIQUE means no duplicate values in that column (or column set), but one NULL may be allowed depending on version/SQL mode — in practice treat UNIQUE email as ‘each email once’.

PRIMARY KEY is UNIQUE + NOT NULL + the main identifier (only one PK). You can have many UNIQUE keys (email, phone).

Trap — UNIQUE vs PRIMARY KEY as identical. Trap: UNIQUE on a column that legitimately repeats (city).

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

Diagram

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

Exam tip

UNIQUE email. PRIMARY KEY roll. Many UNIQUE, one PK.

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 = no duplicate values. PK is unique + not null + the id.
RuleEmail often UNIQUE. Roll often PRIMARY KEY.
TrapUNIQUE on a repeating column.

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