How to implement constraints that go beyond a single row in PostgreSQL?

Question

Examples 1: With 3 tables: Person, Food which has a field called type, and Purchase which links Persons to Food. How can I enforce that a person can only purchase food of the same type from previous purchases if any?

Examples 2: With 5 tables: Session, Auth which has a sessionId and accountId fields, Account, Link which links Accounts to Users, and User. How can I enforce that a Session can only link to Accounts (via Auth) that are linked to the same User (via Link)? In other words, a Session cannot link to Accounts that are linked to different Users.

Thank you!

joanolo · Answer 1 · 2017-07-14T19:27:25.533

For your first example, let's assume your tables are defined in the following way:

CREATE TABLE person
(
    person_id integer PRIMARY KEY,
    person_name text NOT NULL
) ;

CREATE TABLE food
(
    food_id integer PRIMARY KEY,
    food_name text NOT NULL,
    food_type text NOT NULL  /* Unnormalized, for simplicity */
) ;

CREATE TABLE purchase
(
    purchased_at timestamp not null default now(),
    person_id integer NOT NULL REFERENCES person(person_id),
    food_id integer NOT NULL REFERENCES food(food_id),
    PRIMARY KEY (person_id, food_id, purchased_at)
) ;
CREATE INDEX idx_purchaser_food_id 
    ON purchase(food_id, person_id) ;

I would create a function that checks for your constraint. It's a simple SQL statement, with one corner case (the first purchase), and a "literal" translation of your requirement: the current food type is in (the set of already existing ones):

CREATE FUNCTION 
    allowed_food_type_for_person(in _person_id integer, in _food_id integer)
RETURNS 
    boolean AS
$body$
    SELECT
        -- Corner case for first purchase
        (NOT EXISTS 
             (SELECT 
                  *
              FROM 
                  purchase              
              WHERE 
                  person_id = _person_id
             )
        )
        OR
        ( 
           -- Current food types
          (SELECT 
              food_type
          FROM 
              food
          WHERE 
              food_id = _food_id) 
          IN
          -- Existing food types
          (SELECT 
              food_type
          FROM
              purchase
              JOIN food USING(food_id)
          WHERE
              person_id = _person_id)
          )
$body$
LANGUAGE SQL STABLE ;

Now you can add the constraint you wish:

ALTER TABLE purchase 
    ADD CONSTRAINT check_purchase_food_type_is_limited
        CHECK(allowed_food_type_for_person(person_id, food_id));

Now, let's imagine this is your data:

INSERT INTO person
   (person_id, person_name)
VALUES
   (1, 'Alice Cooper'),
   (2, 'Bob Geldorf') ;

INSERT INTO food
   (food_id, food_name, food_type)
VALUES
   (1, 'Banana', 'Fruit'),
   (2, 'Orange', 'Fruit'),
   (3, 'Pear', 'Fruit'),
   (4, 'Lettuce', 'Vegetable'),
   (5, 'Coliflower', 'Vegetable'),
   (6, 'Cellery', 'Vegetable'),
   (7, 'Asparagus', 'Vegetable') ;

You can have 'Alice' do a first purchase:

INSERT INTO 
    purchase
    (person_id, food_id)
VALUES
    (1, 1) ;    -- Alice purchased a Banana (fruit)

Now, she buys an orange, OK:

INSERT INTO 
    purchase
    (person_id, food_id)
VALUES
    (1, 2) ;    -- Alice purchased an Orange (fruit), Ok

But if she tries to buy 'Lettuce':

INSERT INTO 
    purchase
    (person_id, food_id)
VALUES
    (1, 4) ;    -- Alice tries to purchase a Lettuce => fail

ERROR:  new row for relation "purchase" violates check constraint "check_purchase_food_type_is_limited"
DETAIL:  Failing row contains (2017-07-11 19:46:08.830481, 1, 4).

You can play with it at dbfiddle here

For your 2nd example, you'd do something equivalent, but more sophisticated. In any case, there are other ways to solve the "diamond problem" you seem to have, by defining the tables in a different way. Check Data inconsistency prohibition if a table refers to another via two many-to-many relationships, for a very good explanation.

Caveat: As per comment from @DanielVerité: this can have concurrency problems that actual constraints don't have. They can (most probably) be solved by using TRANSACTION ISOLATION LEVEL SERIALIZABLE; incurring the heavy penalty it implies.

_{Side question: Why are you against people having a diverse diet? ;-)}

score 1 · Answer 2 · answered Jul 11 '17 at 20:19

Using an EXCLUSION CONSTRAINT

Examples 1: With 3 tables: Person, Food which has a field called type, and Purchase which links Persons to Food. How can I enforce that a person can only purchase food of the same type from previous purchases if any?

CREATE TABLE person ( pid serial PRIMARY KEY, pname text );
CREATE TABLE food   ( fid serial PRIMARY KEY, fname text );
CREATE TABLE person_food (
  pid int REFERENCES person NOT NULL,
  fid int REFERENCES food   NOT NULL,
  EXCLUDE USING gist( pid WITH =, fid WITH != )
);
INSERT INTO person (pid,pname) VALUES (1,'John'),(2,'Mary');
INSERT INTO food   (fid,fname) VALUES (1,'Pickles'),(2,'Glue');
INSERT INTO person_food (pid,fid) VALUES (1,1); -- works
INSERT INTO person_food (pid,fid) VALUES (1,1); -- works
INSERT INTO person_food (pid,fid) VALUES (1,2); -- boom.
ERROR:  conflicting key value violates exclusion constraint "person_food_pid_fid_excl"
DETAIL:  Key (pid, fid)=(1, 2) conflicts with existing key (pid, fid)=(1, 1).

How to implement constraints that go beyond a single row in PostgreSQL?

2 Answers2

Using an EXCLUSION CONSTRAINT