0

I'm trying to add the following check constraint to the raffle_participant table:

ALTER TABLE raffle_participant ADD CONSTRAINT ck_raffle_participant_total_purchased_le_ticket_limit CHECK (tickets_purchased_in_raffle(raffle_id) <= get_ticket_limit(raffle_id))

When I execute the above command, I get back:

ERROR:  check constraint "ck_raffle_participant_total_purchased_le_ticket_limit" of relation "raffle_participant" is violated by some row

It would appear to me that all the rows within the raffle_participant table satisfy the constraint. What am I not understanding here? Why is the check constraint being violated on the existing data?

Using PostgreSQL version 13.6.

Edit: I've tried the following queries to figure out what rows are violating the constraint and it would appear none of them do.

development=# select (select sum(p.tickets_purchased) from raffle_participant p where p.raffle_id = 2) <= (select r.ticket_limit from raffle r where r.id = 2);
 ?column? 
----------
 t
(1 row)

development=# select (select sum(p.tickets_purchased) from raffle_participant p where p.raffle_id = 3) <= (select r.ticket_limit from raffle r where r.id = 3);
 ?column? 
----------
 t
(1 row)

development=# select (select sum(p.tickets_purchased) from raffle_participant p where p.raffle_id = 4) <= (select r.ticket_limit from raffle r where r.id = 4);
 ?column? 
----------
 t
(1 row)

raffle table

Schema

Column Type Collation Nullable Default
id integer not null generated always as identity
item_name character varying(40) not null
item_value bigint not null
ticket_limit integer not null
ticket_price bigint not null
user_ticket_limit integer not null
ended_on timestamp with time zone
winner_id bigint

Data

id item_name item_value ticket_limit ticket_price user_ticket_limit ended_on winner_id
1 item 10 10 10 2 2022-04-27 17:46:01.271025+00
2 item 10 10 10 10 2022-04-27 17:55:28.783744+00 151150118697959424
3 item 10 10 10 10 2022-04-27 20:32:13.588843+00 151150118697959424
4 item 10 10 10 10

raffle_participant table

Schema

Column Type Collation Nullable Default
raffle_id integer not null
user_id bigint not null
tickets_purchased integer not null

Data

raffle_id user_id tickets_purchased
2 151150118697959424 10
3 151150118697959424 10
4 151150118697959424 3

tickets_purchased_in_raffle function

CREATE FUNCTION tickets_purchased_in_raffle (IN raffle_id raffle.id%TYPE) 
  RETURNS raffle_participant.tickets_purchased%TYPE AS
  $$ 
    SELECT SUM(p.tickets_purchased) FROM raffle_participant p WHERE p.raffle_id = raffle_id 
  $$
LANGUAGE SQL

get_ticket_limit function

CREATE FUNCTION get_ticket_limit (IN raffle_id raffle.id%TYPE) 
  RETURNS raffle.ticket_limit%TYPE AS
  $$
    SELECT r.ticket_limit FROM raffle r WHERE r.id = raffle_id 
  $$
LANGUAGE SQL
jcarrete5
  • 37
  • 2
  • 7
  • this is what failed: `(tickets_purchased_in_raffle(raffle_id) <= get_ticket_limit(raffle_id))` – Daniel A. White May 07 '22 at 22:14
  • What do you mean? Is the syntax of that check constraint invalid? My problem is that I am unable to add the constraint to the table with the shown data. Looking at the data, there appears to be no violating rows. – jcarrete5 May 07 '22 at 22:20
  • no when postgres tries to add the constraint, it tests all rows, and you have some that violates that, so there is at least one row that has (tickets_purchased_in_raffle(raffle_id) > get_ticket_limit(raffle_id)), so make a SELECT and chelc that first – nbk May 07 '22 at 22:24
  • 2
    FYI, what you are doing is not a good idea. From here [Create Table](https://www.postgresql.org/docs/current/sql-createtable.html): "Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row ...". Cheating by using a function is not the way to get around this. There is no dependency tracking on `CHECK`, so it very possible that on migrating this to a new database will fail because the `CHECK` is run before the functions it needs are loaded. For this sort of thing use a trigger. – Adrian Klaver May 07 '22 at 22:34
  • I noticed you did not run a check for `raffle_id = 1`. It is best to take the error as true and look for the exception to the rule that is causing the `CHECK` to fail. Also remember `sum()` where a `raffle_id` does not exist is `NULL` and `NULL <= ` is going to be `NULL` not 't' or 'f'. – Adrian Klaver May 07 '22 at 22:46

1 Answers1

0

The problem is that my input argument, raffle_id, to tickets_purchased_in_raffle was being shadowed by the column name, raffle_id, of raffle_participant. By changing the argument name to rid, I was able to successfully add the check constraint.

jcarrete5
  • 37
  • 2
  • 7
  • 3
    I repeat my warning, do not do this. Using a function and/or referring to rows in another table is going to cause you problems. For a more in depth explanation see [Check Constraints](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS), in particular the **Note** part. – Adrian Klaver May 07 '22 at 22:50
  • Noted. I will look into using triggers for this. Thanks – jcarrete5 May 07 '22 at 22:55