Command Palette

Search for a command to run...

Hectal
Phase 2Beginner3 of 18 in Database Design

Keys, IDs and Constraints

Super, candidate, primary, natural and surrogate keys; ID generation from sequences to UUIDv7 and Snowflake; and the constraints (NOT NULL, UNIQUE, CHECK, FK actions, deferrable, exclusion) that keep data correct.

Keys give rows identity; constraints are rules the database enforces for every client, every time. Pushing rules into the schema is the cheapest correctness you will ever buy.

0/3 · 0%
3 topics ~27 min 5 code blocks & diagrams
Start with the first topic
1
2.1

Keys: Candidate, Primary, Natural and Surrogate

A super key is any set of columns that uniquely identifies a row; a candidate key is a minimal one; the primary key is the candidate you choose, and the others become alternate (UNIQUE) keys. Natural keys come from the business (email, ISBN); surrogate keys are generated (bigint, UUID). Most designs use a surrogate primary key plus UNIQUE constraints on the natural keys.

8 min 1 code practice

2
2.2

ID Generation: Sequences, UUIDv4, UUIDv7 and Snowflake

Sequences give compact, ordered IDs from one database; UUIDv4 gives globally unique random IDs that fragment B-tree indexes; UUIDv7 and Snowflake-style IDs put a timestamp first so they're unique across nodes and still insert in roughly increasing order. Choose by uniqueness scope, ordering, size, and whether IDs may leak information.

10 min 2 code practice

3
2.3

Constraints: The Rules the Database Enforces

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT make invalid data impossible regardless of which service writes it. FK actions (RESTRICT, CASCADE, SET NULL) define what happens on parent changes, deferrable constraints are checked at commit, and PostgreSQL exclusion constraints prevent overlapping ranges such as double bookings.

9 min 2 code practice