2

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!

8
  • 2
    A unique constraint goes beyond a single row and so do exclusion constraint or foreign key constraints. What exactly is your question? Jul 11 '17 at 5:45
  • Here is an example 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 of previous purchases if any?
    – geeko
    Jul 11 '17 at 5:50
  • Surprising scenario... I'd go for a trigger, or a user-defined function for such a case. It is also possible that a sufficiently strange exclusion constraint (that would be more or less an inclusion) would do the trick as well.
    – joanolo
    Jul 11 '17 at 6:29
  • 2
    @geeko edit the question and put all relevant information there, like the example in above comment. What you describe can probably be solved with a simple UNIQUE constraint, if I understand correctly. Jul 11 '17 at 8:09
  • 2
    Constraints are part of the database design as much as tables. For instance the (person,food_type) is a relationship that might be expressed as a table, with a unique constraint, and it doesn't go beyond a single row. Or food_type can be a field in the Person table. Jul 12 '17 at 18:26
1

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

3
  • 2
    What happens if the 3rd insert happens when the 2nd one is not yet committed? I think it passes without error and you end up with what's stored in the db violating the constraint. This is not the case for "real" constraints where concurrent inserts are automatically blocked until commit of other TXs involved in the constraint check. Jul 12 '17 at 18:44
  • @DanielVérité: Very good point. I am not 100% sure. I actually do not (explicitly) lock anything, so, I guess some scenarios with high concurrency could cause some race conditions where this can happen. It also would depend on whether this CHECK is performed at the moment the rows are inserted, or when the transaction is commited. In PostgreSQL, as of 9.6, CHECK constraints cannot be deferred. I guess then, this would only work without risk under ISOLATION LEVEL SERIALIZABLE.
    – joanolo
    Jul 14 '17 at 19:24
  • The idea is interesting but I would not recommend it. It may fail to actually enforce the constraint. Jun 28 '19 at 17:44

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.