The key the business insists on renaming

Difficulty 2/5Subscribers

New to this? Learn it first: Keys: what makes a row that row New to this? Learn it first: Level 1 boss: the signup system

Logistics tracks shipments leaving warehouses.

Every warehouse has a short code that humans use everywhere, such as SP01, RJ02. It is printed on labels, typed into spreadsheets, and read out on the phone. It is genuinely unique, and it is genuinely the thing people mean when they say "which warehouse".

It is also not stable. Operations reorganises regions and renames codes, and they will not stop doing that because a database found it inconvenient.

What has to hold:

Two warehouses can never share a code.
A shipment always belongs to a warehouse that exists.
When a code is renamed, no shipment is lost and none is left pointing at the old code. After the rename, looking up the warehouse's shipments by its new code finds all of them.
A shipment's tracking number is unique across the whole company.

warehouses already exists, and its code is the primary key.

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.

warehouses
code
shipments
idwarehouse_codetracking_number

The schema that already exists

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

prelude.sql

CREATE TABLE warehouses (
  code TEXT PRIMARY KEY,
  city TEXT NOT NULL
);

INSERT INTO warehouses (code, city) VALUES ('SP01', 'Sao Paulo'), ('RJ02', 'Rio de Janeiro');

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…