Populating and Maintaining the Data

Objectives

By the end of this lesson, you should be able to:

• Populate a multi-table schema with data, respecting foreign key order
• Perform realistic UPDATE and DELETE maintenance operations
• Confirm each change against the actual data afterward

💡 Why this matters: A schema with no data is just a diagram made real. This lesson populates every table from Lesson 2, then makes the kind of changes a real business actually makes, a new customer signs up, a product is restocked, an order is cancelled.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the schema from the previous lesson.

Insert Order Follows the Schema

Just like table creation, data has to be inserted in an order that respects foreign keys, a products row can’t reference a category_id that doesn’t exist yet.

INSERT INTO categories (name) VALUES ('Office Supplies'), ('Electronics'), ('Furniture');

INSERT INTO products (name, category_id, price, in_stock) VALUES
  ('Ballpoint Pen Pack', 1, 6.50, true),
  ('Desk Organizer', 1, 12.25, true),
  ('Wireless Mouse', 2, 24.99, true),
  ('Mechanical Keyboard', 2, 89.99, true),
  ('Standing Desk', 3, 349.00, false),
  ('Desk Lamp', 3, 34.50, true);

INSERT INTO customers (name, email, city) VALUES
  ('Riley Nguyen', 'riley.nguyen@example.com', 'Denver'),
  ('Casey Brooks', 'casey.brooks@example.com', 'Austin'),
  ('Morgan Alvarez', 'morgan.alvarez@example.com', 'Seattle'),
  ('Drew Patel', 'drew.patel@example.com', 'Denver');

INSERT INTO employees (first_name, last_name, department, manager_id, hire_date) VALUES
  ('Taylor', 'Nakamura', 'Sales', NULL, '2018-05-05'),
  ('Priya', 'Desai', 'Sales', 1, '2019-01-10'),
  ('Erin', 'Castillo', 'Sales', 1, '2021-03-15');

INSERT INTO employee_profiles (employee_id, bio) VALUES
  (1, 'Sales director overseeing the West region team.'),
  (2, 'Senior account executive focused on enterprise clients.');

INSERT INTO orders (customer_id, employee_id, order_date, status) VALUES
  (1, 2, '2024-01-10', 'completed'),
  (2, 3, '2024-01-20', 'completed'),
  (1, 2, '2024-02-15', 'pending'),
  (3, 3, '2024-02-20', 'cancelled');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 3, 2, 24.99),
  (1, 2, 1, 12.25),
  (2, 4, 1, 89.99),
  (3, 6, 3, 34.50),
  (4, 1, 5, 6.50);

Taylor Nakamura (id 1) has manager_id = NULL, the top of the sales hierarchy, Priya Desai and Erin Castillo both report to her. Order 1 (Riley Nguyen, handled by Priya) contains two products, this is order_items doing its job, one order connecting to multiple products with its own quantity and price per line.

SELECT 'categories' AS tbl, COUNT(*) FROM categories
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'customers', COUNT(*) FROM customers
UNION ALL SELECT 'employees', COUNT(*) FROM employees
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;

     tbl      | count
---------------+-------
 categories   |     3
 products     |     6
 customers    |     4
 employees    |     3
 orders       |     4
 order_items  |     5

Realistic Maintenance: A New Customer Signs Up

INSERT INTO customers (name, email, city) VALUES ('Jamie Kowalski', 'jamie.kowalski@example.com', 'Austin');

Realistic Maintenance: Restocking a Product

UPDATE products SET in_stock = true WHERE name = 'Standing Desk';

SELECT name, in_stock FROM products WHERE name = 'Standing Desk';

     name       | in_stock
-----------------+-----------
 Standing Desk  | t

Realistic Maintenance: An Order Ships

UPDATE orders SET status = 'completed' WHERE id = 3;

SELECT id, status FROM orders WHERE id = 3;

 id |  status
----+------------
  3 | completed

Realistic Maintenance: A Cancelled Order Is Removed

DELETE FROM order_items WHERE order_id = 4;
DELETE FROM orders WHERE id = 4;

SELECT id FROM orders ORDER BY id;

 id
----
  1
  2
  3

Order 4’s line items are deleted first, then the order itself, order_items.order_id actually has ON DELETE CASCADE (Lesson 2), so deleting the order alone would have removed its line items automatically, doing it explicitly here is just being deliberate about the order of operations. Note that a new order inserted after this point would get id = 5, not 4, SERIAL doesn’t reuse a deleted value (Module 3).

Try It

1. Run every INSERT statement above, in order, and confirm the row counts match the table shown in this lesson.
2. Add a new product to the Electronics category, then confirm it appears with SELECT * FROM products WHERE category_id = 2;.
3. Change order 3’s status back to 'pending', then confirm the change, then change it back to 'completed'.
4. Explain, in your own words, why deleting order_items rows before deleting the orders row in this lesson was unnecessary, given ON DELETE CASCADE was already in place.

Recap

• Populating a multi-table schema follows the same dependency order as creating it, tables with no foreign keys first.
• Realistic maintenance is just INSERT, UPDATE, and DELETE (Module 4) applied to genuine business events, a signup, a restock, a status change, a cancellation.
• ON DELETE CASCADE (Module 9) can handle cleanup automatically, but being explicit about deletion order is still a reasonable, defensive habit.

Next lesson: business reports, the complex queries this project actually needs.