SQL · Theory

Primary Key

Theory

72/439

Primary Key

PRIMARY KEY uniquely identifies each row. students.roll or an AUTO_INCREMENT id. Not NULL. Only one PRIMARY KEY per table (it may be two columns together — composite).

InnoDB uses the PK as the clustered index — rows are stored in PK order. A stable integer PK is a good default. Don’t use a name as PK (people change names).

INSERT duplicate PK → error 1062. Foreign keys from other tables point at this PK.

Trap: table with no PK. Trap: VARCHAR UUID PK on a huge table without thinking about InnoDB clustering (advanced, but worth a line if asked).

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

Diagram

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

Exam tip

roll INT PRIMARY KEY or id INT PRIMARY KEY AUTO_INCREMENT.

Primary 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;

Primary Key — keys and constraints protect data integrity; indexes speed lookups.

Short notes

DefPRIMARY KEY = unique + NOT NULL identifier.
RuleOne PK per table. Prefer INT AUTO_INCREMENT for students.
TrapNo PK. Using display names as PK.

SQL · Theory

Primary Key

← All stacks

Theory

72/439

Primary Key

PRIMARY KEY uniquely identifies each row. students.roll or an AUTO_INCREMENT id. Not NULL. Only one PRIMARY KEY per table (it may be two columns together — composite).

InnoDB uses the PK as the clustered index — rows are stored in PK order. A stable integer PK is a good default. Don’t use a name as PK (people change names).

INSERT duplicate PK → error 1062. Foreign keys from other tables point at this PK.

Trap: table with no PK. Trap: VARCHAR UUID PK on a huge table without thinking about InnoDB clustering (advanced, but worth a line if asked).

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

Diagram

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

Exam tip

roll INT PRIMARY KEY or id INT PRIMARY KEY AUTO_INCREMENT.

Primary 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;

Primary Key — keys and constraints protect data integrity; indexes speed lookups.

Short notes

DefPRIMARY KEY = unique + NOT NULL identifier.
RuleOne PK per table. Prefer INT AUTO_INCREMENT for students.
TrapNo PK. Using display names as PK.

Primary Key

Primary Key — sample query

Short notes

Questions

Primary Key

Primary Key — sample query

Short notes

Questions