Project Work Session 1

Goals

By the end of this session you should have:

• All CREATE TABLE IF NOT EXISTS statements written and tested
• A complete TRUNCATE block in the correct FK order
• Data inserted into all root/reference tables (tables with no FK dependencies)
• At least one INSERT with FK resolution via subquery

This is the foundation of the project. Every later session builds on what you write today.

Session Structure

Work Guidance

Work through these steps in order. Do not skip ahead to child tables before the parent tables are fully inserted and tested.

Priority 1 — Schema setup

Write CREATE TABLE IF NOT EXISTS for all tables. Paste the schema from the briefing document and add IF NOT EXISTS to every CREATE TABLE. Run it and verify with:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

You should see all 6 tables listed.

Priority 2 — TRUNCATE block

Write the TRUNCATE block now, even before writing any INSERTs. This is deliberate: writing the TRUNCATE first forces you to think through FK order before you start inserting data.

-- ============================================================
-- RESET: run this at the top of every execution
-- Order: most dependent table first, root tables last
-- ============================================================
TRUNCATE TABLE order_summary  CASCADE;
TRUNCATE TABLE order_items    CASCADE;
TRUNCATE TABLE orders         CASCADE;
TRUNCATE TABLE products       CASCADE;
TRUNCATE TABLE customers      CASCADE;
TRUNCATE TABLE categories     CASCADE;

Run the TRUNCATE block alone and confirm it succeeds. If it fails, you have the dependency order wrong.

Priority 3 — Root table inserts

Insert into categories and customers — these have no FK columns, so they can be inserted in any order. Always include ON CONFLICT DO NOTHING on tables with UNIQUE columns.

-- ============================================================
-- categories  (no FKs)
-- ============================================================
INSERT INTO categories (name) VALUES
    ('Electronics'),
    ('Books'),
    ('Clothing'),
    ('Home & Garden'),
    ('Sports')
ON CONFLICT DO NOTHING;

-- verify
SELECT * FROM categories;

-- ============================================================
-- customers  (no FKs)
-- ============================================================
INSERT INTO customers (full_name, email, joined_date) VALUES
    ('Alice Novak',    'alice@shop.com',   '2024-01-15'),
    ('Boris Petrov',   'boris@shop.com',   '2024-02-20'),
    ('Clara Müller',   'clara@shop.com',   '2024-03-05'),
    ('David Kim',      'david@shop.com',   '2024-04-10'),
    ('Elena Sorokina', 'elena@shop.com',   '2024-05-22')
ON CONFLICT DO NOTHING;

-- verify
SELECT * FROM customers;

Priority 4 — First FK-resolved insert

Now insert products, which depends on categories. Use a subquery to look up the category_id:

-- ============================================================
-- products  (FK: category_id -> categories)
-- ============================================================
INSERT INTO products (name, category_id, price, stock) VALUES
    ('Laptop Pro 15',
     (SELECT category_id FROM categories WHERE name = 'Electronics'),
     1299.99, 50),
    ('Wireless Mouse',
     (SELECT category_id FROM categories WHERE name = 'Electronics'),
     29.99, 200),
    ('PostgreSQL for Beginners',
     (SELECT category_id FROM categories WHERE name = 'Books'),
     34.95, 75),
    ('Running Shoes X200',
     (SELECT category_id FROM categories WHERE name = 'Sports'),
     89.90, 120),
    ('Garden Hose 20m',
     (SELECT category_id FROM categories WHERE name = 'Home & Garden'),
     24.50, 60)
ON CONFLICT DO NOTHING;

-- verify
SELECT p.name, c.name AS category, p.price
FROM products p
JOIN categories c USING (category_id);

Checkpoint Questions

The instructor will ask each student these questions during the checkpoint:

1. What is your insert order? Walk me through the FK dependency tree.
2. How many tables have FK dependencies? Which columns?
3. Show me your TRUNCATE block. Run it — does it succeed?
4. Show me one INSERT that uses a subquery for FK resolution. Explain why you did not use a number.
5. Run SELECT COUNT(*) on each table you have inserted so far.

Troubleshooting Common Issues

Error: insert or update on table "X" violates foreign key constraint

You are trying to insert a child row before its parent exists. Check your insert order and make sure parent tables are fully committed before you insert into child tables.

Error: duplicate key value violates unique constraint

You forgot ON CONFLICT DO NOTHING on a table with a UNIQUE constraint, or you ran the INSERT before the TRUNCATE. Add ON CONFLICT DO NOTHING and always run the full script from the top.

Subquery returns zero rows

The parent row you are looking up does not exist yet, or the value you are searching for does not match exactly (check for trailing spaces or wrong capitalisation). Run a plain SELECT first to confirm the parent row is there.

-- Debug: check if the parent row exists
SELECT category_id FROM categories WHERE name = 'Electronics';

TRUNCATE fails with FK violation

Your TRUNCATE order is wrong. The table you are truncating still has child rows referencing it. Use CASCADE to let PostgreSQL handle it automatically, or fix the dependency order.