SQL · Theory

Primary Key vs Unique key

Theory

270/439

Primary Key vs Unique key

Both unique. PK: one per table, NOT NULL, clustered in InnoDB. UNIQUE: many allowed, NULL handling differs, secondary index.

id INT PRIMARY KEY, email VARCHAR UNIQUE is the classic pair.

Trap — two PRIMARY KEYs. Impossible. Two UNIQUE keys. Fine.

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

Diagram

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

Exam tip

One PK. Many UNIQUE (email, phone).

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

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

Short notes

DefPK = the identifier. UNIQUE = extra no-duplicate columns.
RuleOne PK, many UNIQUE. PK NOT NULL.
TrapTwo PKs.

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 vs Unique key

Primary Key vs Unique key — sample query

Short notes

Questions

Primary Key vs Unique key

Primary Key vs Unique key — sample query

Short notes

Questions