A candidate key is a minimal column set the schema declares can identify a row uniquely. This lesson selects customers.id and orders.id as primary keys; orders.customer_id references customers.id.
Engine-neutral Foundation 01
Tables, rows, and keys
What does the schema allow, and which key conditions do the current rows already establish?
Separate a schema from its current rows, then test how primary and foreign keys decide whether a proposed order is valid.
Concept · relationalDerived · fixture
What does the schema allow, and which key conditions do the current rows already establish?
customers holds customer rows and orders holds order rows. orders.customer_id → customers.id connects them.
Separate the schema from current rows
The schema defines which columns and constraints customers and orders allow. Current rows are the values stored under that structure right now.
Schema definition
CREATE TABLE customers (
id INTEGER NOT NULL,
name VARCHAR(80) NOT NULL,
country_code CHAR(2) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE orders (
id INTEGER NOT NULL,
customer_id INTEGER NOT NULL,
status VARCHAR(20) NOT NULL,
ordered_at TIMESTAMP NOT NULL,
total_cents INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);Current rows
customers · parent rows
| id | name | country_code |
|---|---|---|
101 | Mina | KR |
102 | Noah | US |
103 | Lina | DE |
orders · current child rows
| id | customer_id | status | ordered_at | total_cents |
|---|---|---|---|---|
5001 | 101 | PAID | 2026-07-28 09:10:00 | 3200 |
5002 | 102 | PENDING | 2026-07-28 10:20:00 | 7500 |
5003 | 101 | SHIPPED | 2026-07-29 13:40:00 | 12800 |
Choose a proposed order
Only one or two values change between the rows. Predict from the schema and current rows first.
Predict the result
Only one or two values change between the rows. Predict from the schema and current rows first.
Constraint checks and final rows
Only one or two values change between the rows. Predict from the schema and current rows first.
orders · current child rows
| id | customer_id | status | ordered_at | total_cents |
|---|---|---|---|---|
5001 | 101 | PAID | 2026-07-28 09:10:00 | 3200 |
5002 | 102 | PENDING | 2026-07-28 10:20:00 | 7500 |
5003 | 101 | SHIPPED | 2026-07-29 13:40:00 | 12800 |
Review terms
schema- The structure that defines a table's columns, data types, keys, and constraints.
current rows- The values stored in the table under that schema right now.
relation / table- A relation in the relational model and its SQL table representation, whose rows share one column structure.
row- One item representing an entity or fact, with one value for each column.
column- An attribute with the same meaning and domain across every row.
candidate key- A minimal column set the schema declares can identify a row uniquely; coincidentally distinct sample values are not enough.
primary key- The candidate key selected as the row's primary identity.
foreign key- A constraint declaring that columns in one table reference a candidate or primary key in another.
referential integrity- The rule that keeps a foreign-key value connected to an allowed parent row.
Source boundary
The results apply this lesson's constraints to fixed fixture rows to explain relational concepts. For a deterministic explanation, the lesson checks the primary key before the foreign key. This is not a claim about one DB engine's error order or physical storage.
- Concept · relationalRelational schema and constraints
Schema, table/row/column, candidate, primary, and foreign keys, and referential integrity
- Derived · fixtureEngine Atlas lesson fixture
Exact decisions from the checked-in customers/orders rows and the two ordered constraint checks
Complete lesson without JavaScript
The schema defines which columns and constraints customers and orders allow. Current rows are the values stored under that structure right now.
A candidate key is a minimal column set the schema declares can identify a row uniquely. This lesson selects customers.id and orders.id as primary keys; orders.customer_id references customers.id.
CREATE TABLE customers (
id INTEGER NOT NULL,
name VARCHAR(80) NOT NULL,
country_code CHAR(2) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE orders (
id INTEGER NOT NULL,
customer_id INTEGER NOT NULL,
status VARCHAR(20) NOT NULL,
ordered_at TIMESTAMP NOT NULL,
total_cents INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);customers · parent rows
| id | name | country_code |
|---|---|---|
101 | Mina | KR |
102 | Noah | US |
103 | Lina | DE |
orders · current child rows
| id | customer_id | status | ordered_at | total_cents |
|---|---|---|---|---|
5001 | 101 | PAID | 2026-07-28 09:10:00 | 3200 |
5002 | 102 | PENDING | 2026-07-28 10:20:00 | 7500 |
5003 | 101 | SHIPPED | 2026-07-29 13:40:00 | 12800 |
Results for the three proposed rows
- Reuse an existing order id
orders.id already exists, so the PRIMARY KEY (id) constraint named orders.primary-key rejects the insert.
- Reference a missing customer
No customers.id row matches orders.customer_id, so the FOREIGN KEY (customer_id) constraint named orders.customer-foreign-key rejects the insert.
- Connect a new order to an existing customer
orders.id passes PRIMARY KEY (id) and orders.customer_id references an existing customers.id, so FOREIGN KEY (customer_id) also passes and the proposed row is added to orders.
The results apply this lesson's constraints to fixed fixture rows to explain relational concepts. For a deterministic explanation, the lesson checks the primary key before the foreign key. This is not a claim about one DB engine's error order or physical storage.