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

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!

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).

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? ;-)}