The order with no customer

Difficulty 1/5Free

New to this? Learn it first: Foreign keys: the row that points at nobody New to this? Learn it first: Delete actions: what happens to the rows left behind

A small shop records the orders its customers place.

Two rules, and the business treats both as obvious:

Every order belongs to a customer who exists. An order pointing at nobody cannot be shipped, invoiced or refunded, and it is the kind of row that is only discovered months later by a report that will not balance.
A customer places as many orders as they like. Repeat business is the point, so nothing may stop a second order.

There is a third rule nobody states until it happens: a customer who has orders cannot simply be erased, because the orders would be left pointing at a row that is gone.

customers already exists.

The contract

2 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

customers
id
orders
idcustomer_id

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

INSERT INTO customers DEFAULT VALUES;
INSERT INTO customers DEFAULT VALUES;

Your schemaPostgreSQL DDL: tables, constraints, indexes

schema.sql

-- customers already exists and holds ids 1 and 2. Do not create it again.

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT
);

-- customer_id holds a number today. Nothing checks whose number it is.

Tab indents · ⌘/Ctrl + Enter runs

What will be checked

4 tests

····An order for an existing customer goes in
····An order for a customer who does not exist is refused
····The same customer can place a second order
····A customer with orders cannot be deleted out from under them