4

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.

  • You know that these 2 designs are not equivalent. Did you mean to have UNIQUE constraints on both first_session and last_session? – ypercubeᵀᴹ Mar 6 '17 at 10:04
  • I know, it was just an example. The idea behind it is to avoid overlapping ranges and the usage of the ranges instead of two columns is meant to compensate the multiple UNIQUE contraints and triggers to check the overlapping. – Matjaž Mar 6 '17 at 11:03
7

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;
    
  • I had a trigger to check the overlapping, but dynamically generating the ranges for the constraint is something I did not think about. Thanks. – Matjaž Mar 6 '17 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.