Constraints and Indexes

Primary Key

Stable Row Identity

A primary key identifies rows. It is the column other tables often reference.

Program

Play the script to create stable user IDs.

primary_key.sql
Replay: real traced execution (multi-file project)
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users VALUES (1, 'Ada'), (2, 'Lin');
SELECT id, name FROM users ORDER BY id;

  1. tables ← 1 row

    1CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);2INSERT INTO users VALUES (1, 'Ada'), (2, 'Lin');
    values this step1 rowtables

  2. users ← 2 rows

    1CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);2INSERT INTO users VALUES (1, 'Ada'), (2, 'Lin');3SELECT id, name FROM users ORDER BY id;
    values this step2 rowsusers

  3. result ← 2 rows

    2INSERT INTO users VALUES (1, 'Ada'), (2, 'Lin');3SELECT id, name FROM users ORDER BY id;
    values this step2 rowsresult

Follow the Rows

  1. users has id INTEGER PRIMARY KEY and name.
  2. The script inserts id 1 with name Ada.
  3. It also inserts id 2 with name Lin.
  4. ORDER BY id keeps 1 before 2.
  5. The final rows are 1 Ada and 2 Lin. | id | name | | --- | --- | | 1 | Ada | | 2 | Lin |
PRIMARY KEY `PRIMARY KEY` marks a row identifier.
identity IDs make rows easy to reference from other tables.
ordering Primary keys are often used for stable output order.

Exercise: primary_key.sql

Reproduce the rows 1 Ada and 2 Lin, then identify which column gives each row its stable id.