Master how relational database tables are structured, normalized, and connected in production web applications. Explore Primary Keys, Foreign Keys, parent-child hierarchies, and the three core relationship patterns. Experiment in a live database simulator to test referential actions, cascade deletions, and constraint violations.
In a relational database, a table represents a distinct real-world entity (e.g., users, products, orders). Each table is structured into rows (individual records or instances) and columns (strongly typed attributes).
A column (or set of columns) that guarantees every row in the table can be uniquely identified. A primary key must be UNIQUE and cannot be NULL.
A column in a child table that references the primary key of a parent table. It establishes a link between entities and allows the database engine to enforce connection rules.
The database guarantee that a foreign key value must always point to an existing, valid parent row. The engine strictly prevents creating orphaned records.
-- ❌ THE "MEGA-TABLE" DISASTER: Storing user info, order info, and product info in one table: -- user_id | user_name | user_address | order_id | order_date | product_name | product_price -- 1 | Alex R. | 123 Main St | 101 | 2026-09-01 | Laptop | $1,200.00 -- 1 | Alex R. | 123 Main St | 101 | 2026-09-01 | Wireless Mouse| $25.00 -- 1 | Alex R. | 123 Main St | 102 | 2026-09-03 | USB-C Cable | $15.00 -- Disasters caused by this flat structure: -- 1. Data Duplication: "Alex R." and "123 Main St" are re-written hundreds of times. -- 2. Update Anomalies: If Alex moves, updating 1 row leaves 99 rows with obsolete addresses! -- 3. Deletion Anomalies: Deleting order 102 accidentally erases the only record of the USB-C Cable!
Full-stack applications model real-world business domains using three primary relational patterns:
A single record in Table A relates to at most one record in Table B.
Full Stack Use Case: Isolating sensitive user auth credentials (users) from optional extended public bio details (user_profiles). The foreign key has a UNIQUE constraint.
A single record in Table A can relate to multiple records in Table B, but each record in Table B belongs to exactly one record in Table A.
Full Stack Rule: The foreign key (user_id) always resides in the child table (orders), never in the parent table.
Multiple records in Table A relate to multiple records in Table B.
The Junction Table: You cannot place a single foreign key to link N:N. You must create a bridge table (enrollments) holding student_id (FK) and course_id (FK).
Here is how an e-commerce platform links users, shopping carts, orders, and products with referential integrity:
When a customer clicks "Checkout" in your web app, the backend creates one entry in orders, and multiple entries in order_items, each pointing to their respective product_id.
Actively test how foreign keys protect your database. Insert valid orders, attempt invalid foreign keys to see referential constraint rejections, and test parent record deletions:
| id (PK) | name | action | |
|---|---|---|---|
| 1 | Alex Rivera | alex@example.com | |
| 2 | Sam Chen | sam@example.com | |
| 3 | Taylor Swift | taylor@example.com |
| id (PK) | user_id (FK) | total | status |
|---|---|---|---|
| 101 | 1 ✓ | $149.50 | delivered |
| 102 | 1 ✓ | $89.00 | shipped |
| 103 | 2 ✓ | $299.00 | pending |
Database constraints are not just theoretical rules — they are the defensive guard rails that protect your production data from being corrupted by buggy backend logic:
Enforces uniqueness and not-null on the identity column. Every table should possess a primary key.
Guarantees referential integrity: child rows cannot point to a non-existent parent ID.
NOT NULL guarantees data presence; UNIQUE prevents duplicate values (e.g. user emails).
Deleting the parent row automatically deletes all corresponding child rows.
Best for: Dependent child data that has no reason to exist without the parent (e.g., deleting an Order removes its order_items).
The database halts and throws an error if any child row references this parent.
Best for: High-value business records (e.g., preventing deletion of a users account if historic financial transactions or tax invoices exist).
The child row is preserved, but its foreign key column is updated to NULL.
Best for: Optional relationships (e.g., if an author is deleted, their blog posts remain with author_id = NULL).
How do relational tables translate into real-world web experiences? When a user navigates to their profile page in a React app, here is the exact full-stack pipeline:
// 1. Frontend sends HTTP Request:
// GET https://api.myshop.com/users/42/orders
// 2. Node.js Backend receives request and executes an SQL JOIN:
SELECT
u.id AS user_id,
u.name,
o.id AS order_id,
o.total,
o.status
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 42;
// 3. Database utilizes the Foreign Key index to rapidly match rows.
// 4. Backend serializes the related rows into structured JSON:
{
"user_id": 42,
"name": "Alex Rivera",
"orders": [
{ "order_id": 101, "total": 149.50, "status": "delivered" },
{ "order_id": 102, "total": 89.00, "status": "shipped" }
]
}
// 5. React Frontend receives the JSON and renders the User Order Dashboard!Analyze real database error messages encountered in production. Diagnose the root cause and learn the defensive remedy:
ERROR: insert on table "enrollments" violates foreign key constraint "fk_enrollment_course" Detail: Key (course_id)=(999) is not present in table "courses".
Why did the database engine reject this INSERT statement?
DESIGN ERROR: Attempted to store comma-separated course IDs inside users: "10,24,35" Consequence: Cannot enforce foreign keys, impossible to index, painful JOIN queries.
How should a Many-to-Many relationship between Students and Courses be correctly designed?
ERROR: null value in column "user_id" of relation "orders" violates not-null constraint Detail: Failing row contains (204, null, 75.00, 'pending').
What is the consequence of marking a foreign key column as NOT NULL in orders?
ERROR: duplicate key value violates unique constraint "users_pkey" Detail: Key (id)=(1) already exists.
What fundamental rule of relational databases was violated?
Put your relational architectural knowledge to the test. Design the database schema for a learning platform with Users, Courses, Lessons, and Enrollments: