Partial index on value from related table, rather than foreign key?

Question

I'm working on a learning platform where students belong to a team, each of which belongs to a curriculum:

CREATE TABLE teams (
    id SERIAL,
    name string NOT NULL,
    curriculum_id integer NOT NULL
);

CREATE TABLE curricula (
    id SERIAL,
    name string NOT NULL
);

CREATE UNIQUE INDEX index_curricula_on_name ON curricula USING btree (name);

Curricula have to be unique by name, and while most curricula are allowed to have multiple teams associated to them, one can not. I am trying to add a partial (unique) index on the teams table so as to add a restraint on the curriculum.

I know I can partially constrain the curriculum id itself with...

CREATE UNIQUE INDEX index_teams_on_curriculum_id ON teams USING btree (curriculum_id)
WHERE curriculum_id = 1;

... but this is not viable, as the IDs for the curriculum will vary across environments (dev, staging, etc).

Is there a way to constrain the teams.curriculum_id column by curricula.name instead?

@erwin thanks for the edits and better tags! – kevlarr Feb 23 '16 at 16:30 — kevlarr, Feb 23 '16 at 16:30

score 4 · Accepted Answer · edited May 23 '17 at 12:17

You could implement something like this with a trigger or with a fake immutable function in a CHECK constraint. Both have their weak spots.

But this can also be implemented with pure SQL - only using NOT NULL, CHECK, UNIQUE and FK constraints. No weak spot.

CREATE TABLE curriculum (
   curriculum_id serial PRIMARY KEY
 , curriculum    text UNIQUE NOT NULL
 , team_unique   boolean UNIQUE NOT NULL
 , CONSTRAINT curriculum_team_uni UNIQUE (curriculum_id, team_unique)  -- for multicolumn FK
);

CREATE TABLE team (
   team_id       serial PRIMARY KEY
 , team          text NOT NULL
 , curriculum_id integer NOT NULL
 , team_unique   boolean NOT NULL
 -- , CONSTRAINT fk1 FOREIGN KEY (curriculum_id) REFERENCES curriculum
 , CONSTRAINT fk2 FOREIGN KEY (curriculum_id, team_unique)
              REFERENCES curriculum (curriculum_id, team_unique)
);

CREATE UNIQUE INDEX team_curriculum_uni_idx ON team (team_unique)
WHERE team_unique;

Add a boolean NOT NULL column to parent and child table and make it UNIQUE in the parent table. So only one row in curriculum can be marked unique - to implement your restrictive requirement:

one can not

A partial unique index team_curriculum_uni_idx enforces only a single reference to it.

If there were multiple unique curriculums (to be referenced once only), we would remove the UNIQUE constraints on curriculum.team_unique and extend the partial unique index on team to (curriculum_id, team_unique).
The FK (fk2) forces to inherit the combination of columns.
This makes it simple to add a UNIQUE constraint to enforce a single team for the unique curriculum.
The default MATCH SIMPLE behavior of Postgres FK constraints only enforces combinations without NULL values. We can either use MATCH FULL or another plain FK (fk1) to enforce only existing curriculum_id. I commented the additional FK since we don't need it in this configuration (both FK columns defined NOT NULL).

SQL Fiddle.

This is ridiculously phenomenal, thank you. It's going to be a bit before I can read through all of that and implement, but I wanted to thank you in advance. — kevlarr, Feb 23 '16 at 16:32

Partial index on value from related table, rather than foreign key?

1 Answers1