Composite primary keys in SQL

A join table's identity is the pair of things it joins, not a new number.

CREATE TABLE cart_items (
    cart_id INTEGER NOT NULL,
    sku TEXT NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
    added_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (cart_id, sku)
) WITHOUT ROWID;

How it works

1. PRIMARY KEY (a, b) makes the pair the row's identity.
2. It doubles as the uniqueness rule and as the index you would add anyway.
3. WITHOUT ROWID stores the row in that key's own B-tree.

Keywords and builtins used here

• CHECK
• CREATE
• CURRENT_TIMESTAMP
• DEFAULT
• INTEGER
• KEY
• NOT
• NULL
• PRIMARY
• TABLE
• TEXT
• WITHOUT

The run, in numbers

Lines

7

Characters to type

222

Tokens

45

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 32 seconds.

Type this snippet

Step 3 of 5 in Constraints, step 3 of 16 in Schema & constraints.

← Previous Next →