• Each organisation has a unique name.

• Within each organisation can be multiple teams, whose names should be unique, but only within their own organisation.

• Also, each team can have projects, which names again should be unique per team.

Overall, expect for the organisation, names should be unique per their own "group", but can be duplicates otherwise.

I think I would need to create a constraint that CHECKs whether a given name already exists in the "overlying group", but I struggle to do this with the complex SQL (exclusion) constraints.

Would it be possible to write such a constraint ?

CodePudding user response：

Assuming this is for PostgreSQL based on the two other tags (Postgresql & Supabase).

You can do this directly in the primary keys. Here's how the bare bones of such a structure would look like:

CREATE TABLE organization (
   org_name text  NOT NULL,
   PRIMARY KEY (org_name)
);

CREATE TABLE teams (
   org_name text NOT NULL,
   team_name text NOT NULL,
   PRIMARY KEY (org_name, team_name),
   FOREIGN KEY (org_name)
      REFERENCES organization (org_name)
);

CREATE TABLE projects (
   org_name text NOT NULL,
   team_name text NOT NULL,
   project_name text NOT NULL,
   PRIMARY KEY (org_name, team_name, project_name),
   FOREIGN KEY (org_name)
      REFERENCES organization (org_name),
   FOREIGN KEY (org_name, team_name)
      REFERENCES teams (org_name, team_name)
);

Notes, this will allow things like ORg Contoso & Org CONTOSO you may want to pick citext instead of text as the type to avoid such names.