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)
('2016-04-01', 4, 140, 15)
('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:
but it is about an Oracle database and is slightly different.
Thanks for any suggestions!!!