Exactly one of them is the main one

Difficulty 3/5Subscribers

New to this? Learn it first: UNIQUE: no two alike, or no two among some New to this? Learn it first: Level 1 boss: the signup system

People keep more than one address on file: home, work, the place a parcel should go this week.

The rules:

One person can have many addresses. That part is ordinary.
At most one of a person's addresses is the primary one. Two primaries is not a preference, it is a bug: the shipping label has to pick one.
Two different people can each have their own primary. The rule is per person, not global.
Deleting a person takes their addresses with them. Nobody wants an address belonging to nobody.
An address always has a label, and it is one of home, work or other.

Zero primaries is fine, because someone can add addresses and choose later.

people 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.

people
idname
addresses
idperson_idlabelis_primary

The schema that already exists

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

prelude.sql

CREATE TABLE people (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO people (name) VALUES ('Ana'), ('Bruno');

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…