What row is violating this check constraint?-CodePudding

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	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	Nullable
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

CodePudding user response：

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.