Foreign key on PostgreSQL range bounds

Question

Is it possible in PostgreSQL 9.6 to apply a foreign key constraint on the lower and upper bounds of a range (of integers in my case)?

Currently I keep the lower bound and upper bound in two columns, but would like to unify them into a range column while keeping the foreign key references.

Example

I have a table containing a list of sessions, something like:

CREATE TABLE sessions (
    session_id    SERIAL      PRIMARY KEY,
    session_start TIMESTAMPTZ NOT NULL,
    description   TEXT
);

and then a table of groups of those sessions which is currently expressed as:

CREATE TABLE session_groups (
    group_id      SERIAL  PRIMARY KEY,
    first_session INTEGER NOT NULL UNIQUE,
    last_session  INTEGER NOT NULL UNIQUE,
    description   TEXT,

    FOREIGN KEY (first_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (last_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,

    CONSTRAINT last_session_after_first
         CHECK (last_session >= first_session)
);

What I would like:

CREATE TABLE session_groups (
    group_id      SERIAL    PRIMARY KEY,
    session_range INT4RANGE NOT NULL UNIQUE,
    description   TEXT,

    FOREIGN KEY (lower(session_range))
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (upper(session_range))
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT

    -- Dropped last_session_after_first constraint since
    -- the INT4RANGE type checks it automatically.
);

which is throwing a syntax error.

Answer 1

It is not allowed to have arbitrary expressions in FOREIGN KEY constraints, only columns. That's why you get an error when you try the 2nd approach.

You can however, use a VIEW to access the table:

CREATE VIEW v_session_groups 
    (group_id, session_range, description) AS
SELECT
    group_id,
    int4range(first_session, last_session, '[)'),
    description
FROM 
    session_groups ;

You could even add triggers, and then be able to INSERT/UPDATE/DELETE from the view, as if it was a normal table.

---

Side notes:

• The first design has 2 UNIQUE constraints, on (first_session) and (last_session), while the 2nd has 1 UNIQUE constraint, on (first_session, last_session). These are not equivalent designs, so re-examine your requirements.
• I used the default '[)' (inclusive-exclusive) parameter for the int4range column. You may want to change that to '[]' (all-inclusive), depending on how you want to be saving ranges.
  Examples:int4range(1,1) and int4range(1,1,'[)') is the empty range while int4range(1,1,'[]') is the [1,2) range.

• If you want to enforce / avoid overlapping ranges, then you don't need a UNIQUE constraint at all but an EXCLUDE constraint:

  CREATE TABLE session_groups (
      group_id      SERIAL  PRIMARY KEY,
      first_session INTEGER NOT NULL,
      last_session  INTEGER NOT NULL,
      description   TEXT,
  
      FOREIGN KEY (first_session)
                  REFERENCES sessions (session_id)
                  ON UPDATE CASCADE
                  ON DELETE RESTRICT,
      FOREIGN KEY (last_session)
                  REFERENCES sessions (session_id)
                  ON UPDATE CASCADE
                  ON DELETE RESTRICT,
  
      CONSTRAINT non_overlapping_sessions
          EXCLUDE  USING gist
          ( int4range(first_session, last_session, '[)') WITH && )
  );
  

  See also the documentation on constraints on range types. You probably need to add the extension as well:

  CREATE EXTENSION btree_gist;