Adding a unique index or constraint that checks other rows

I have a table containing redemptions, each of which has a user_id. I'm trying to prevent a user from performing more than one redemption in 24 hours.

The SQL I've tried so far is:

ALTER TABLE "redemptions"
ADD CONSTRAINT "one_redemption_per_user_per_twenty_four_hours"
CHECK (
  NOT EXISTS(
    SELECT u from users WHERE u.inserted_at >= now()   INTERVAL '24 hours'
    AND user_id = u.user_id;
  )
)

Which results in ** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near ";"

Is there a better way to achieve what I'm trying to do? If not, how can I fix the above?

Thanks, Mark

EDIT: Redemption table structure as requested:

|  id  |  user_id  | voucher_id  |  inserted_at  | updated_at

CodePudding user response：

As explained in the manual enter link description here

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row (see Section 5.4.1). The system column tableoid may be referenced, but not any other system column.

Even it this was supported, the performance would be horrible.

There is a better way to do it.

CREATE EXTENSION IF NOT EXISTS btree_gist;

create table redemption(
 id bigserial, 
 user_id bigint,
 voucher_id bigint,
 validity tstzrange, 
 EXCLUDE USING GIST (user_id WITH =, validity WITH &&)
);

The constraint ensures that a user_id, at a given point in time, has only one active voucher.

Example.

Step 1: user 1 used voucher 2 just now. The validity is 24 hours.

INSERT INTO redemption(user_id,voucher_id,validity) 
VALUES
(1,2,tstzrange(now(),now() '24 hours'::interval));

Step 2: user 1 tries to use another voucher (3) in 12 hours:

INSERT INTO redemption(user_id,voucher_id,validity)
VALUES
(1,3,tstzrange(now() '12 hours'::interval,now() '36 hours'::interval));

PostgreSQL replies with:

ERROR:  conflicting key value violates exclusion constraint "redemption_user_id_validity_excl"
DETAIL:  Key (user_id, validity)=(1, ["2022-10-28 00:01:56.422834 02","2022-10-29 00:01:56.422834 02")) conflicts with existing key (user_id, validity)=(1, ["2022-10-27 12:00:45.750125 02","2022-10-28 12:00:45.750125 02")).

PostgreSQL automatically check that is no overlap in the validity range for a given user.

The performance is guaranteed by the fact that the CHECK contraint automatically creates an appropriate index. Here the table a reported by psql:

\d  redemption
                                                   Table "public.redemption"
   Column   |   Type    | Collation | Nullable |                Default                 | Storage  | Stats target | Description 
------------ ----------- ----------- ---------- ---------------------------------------- ---------- -------------- -------------
 id         | bigint    |           | not null | nextval('redemption_id_seq'::regclass) | plain    |              | 
 user_id    | bigint    |           |          |                                        | plain    |              | 
 voucher_id | bigint    |           |          |                                        | plain    |              | 
 validity   | tstzrange |           |          |                                        | extended |              | 
Indexes:
    "redemption_user_id_validity_excl" EXCLUDE USING gist (user_id WITH =, validity WITH &&)
Access method: heap

Additionally, it makes probably sense to add another constraint about a voucher being unique, to avoid the case where a voucher is used multiple times.

Thus:

create table redemption(
 id bigserial, 
 user_id bigint,
 voucher_id bigint UNIQUE,
 validity tstzrange, 
 EXCLUDE USING GIST (user_id WITH =, validity WITH &&)
);