Schema Design Basics

Primary Key Lookup

Stable Row Identity

A primary key gives each row a stable identity. Queries can use that key to find exactly one intended row.

Program

Play the script to choose a product SKU and fetch the matching product row.

target_sku
primary_key_lookup.sql
Replay: real traced execution (multi-file project)
CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price INTEGER);
INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);
WITH params(target_sku) AS (VALUES ('B2')) SELECT sku, name, price FROM products WHERE sku = (SELECT target_sku FROM params);

CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price INTEGER);
INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);
WITH params(target_sku) AS (VALUES ('A1')) SELECT sku, name, price FROM products WHERE sku = (SELECT target_sku FROM params);

CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price INTEGER);
INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);
WITH params(target_sku) AS (VALUES ('C3')) SELECT sku, name, price FROM products WHERE sku = (SELECT target_sku FROM params);

  1. tables ← 1 row

    1CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price INTEGER);2INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);
    values this step1 rowtables

  2. products ← 3 rows

    1CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price INTEGER);2INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);3WITH params(target_sku) AS (VALUES ('B2')) SELECT sku, name, price FROM products WHERE sku = (SELECT target_sku FROM params);
    values this step3 rowsproducts

  3. result ← 1 row

    2INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);3WITH params(target_sku) AS (VALUES ('B2')) SELECT sku, name, price FROM products WHERE sku = (SELECT target_sku FROM params);
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price INTEGER);2INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);
    values this step1 rowtables

  2. products ← 3 rows

    1CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price INTEGER);2INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);3WITH params(target_sku) AS (VALUES ('A1')) SELECT sku, name, price FROM products WHERE sku = (SELECT target_sku FROM params);
    values this step3 rowsproducts

  3. result ← 1 row

    2INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);3WITH params(target_sku) AS (VALUES ('A1')) SELECT sku, name, price FROM products WHERE sku = (SELECT target_sku FROM params);
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price INTEGER);2INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);
    values this step1 rowtables

  2. products ← 3 rows

    1CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price INTEGER);2INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);3WITH params(target_sku) AS (VALUES ('C3')) SELECT sku, name, price FROM products WHERE sku = (SELECT target_sku FROM params);
    values this step3 rowsproducts

  3. result ← 1 row

    2INSERT INTO products VALUES ('A1', 'pen', 3), ('B2', 'notebook', 7), ('C3', 'bag', 25);3WITH params(target_sku) AS (VALUES ('C3')) SELECT sku, name, price FROM products WHERE sku = (SELECT target_sku FROM params);
    values this step1 rowresult
primary key `sku TEXT PRIMARY KEY` says each SKU identifies one product row.
lookup `WHERE sku = target_sku` finds the row with the selected key.
stable identity A key value is safer for lookup than a display name that might change.