Constraint on row value based on values in other rows

Question

Is it possible to have a table with four columns:

CREATE TABLE accrual (
 add_date date NOT NULL,
 user_id integer NOT NULL,
 rate integer NOT NULL,
 amount numeric(7,3)
); 

and then constrain it so that if I already have one entry like

('2016-04-01', 3, 120, 25.6)

an attempt to insert another entry with the same date and user_id but a different rate would fail? i.e.

('2016-04-01', 3, 140, 15)

would fail, but

('2016-04-02', 3, 140, 15)

or

('2016-04-01', 4, 140, 15)

or

('2016-04-01', 3, 120, 15)

would be fine?

To be clear, the date and user_id are basically like an index, except duplicate indices are totally fine as long as the rates are the same. The amount is not constrained.

I am working with a postrgesql 9.5 database, and I am using PHP. My guess is that the easiest thing to do is to write some PHP code to perform the check. The closest question I could find to this was this one:

trigger or check constraint insert values based upon existence of others

but it is about an Oracle database and is slightly different.

Thanks for any suggestions!!!

• Neil McGuigan: Exclusion constraint might work here. see postgresql.org/docs/current/static/… . Else you'll need a trigger

Answer 1

As @NeilMcGuigan suggested, an Exclusion Constraint is the key here. The part I was unfamiliar with was the ability to use the <> operator. As available post Postgres 9.0:

CREATE TABLE accrual (
 add_date date NOT NULL,
 user_id integer NOT NULL,
 rate integer NOT NULL,
 amount numeric(7,3),
 EXCLUDE USING gist
 (date WITH =,
 user_id WITH =,
 rate WITH <>)
);

Two helpful links are:

1. http://www.tutorialspoint.com/postgresql/postgresql_constraints.htm -- a short tutorial on constraints in Postgres, with an example for the Exclusion constraint that uses the <> operator

and

2. http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints (specifically slide 23) -- a presentation on the need for and use of Exclusion constraints. Slide 23 introduces the <> operator and its usefulness.

• Neil McGuigan: Nice one! Glad you figured it out