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.

primary_key_lookup.sql 
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 ()) 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 ()) 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 ()) SELECT sku, name, price FROM products WHERE sku = (SELECT target_sku FROM params);
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.