Composite Key with a column that auto increments and reset when foreign key value changes

I am trying to implement a ticket system, the numbers generated would be sequential. 4000, 4001, 4002, 4003 My idea was to have a table that relates to current raffle, and the ticket_no column is an auto increment column that resets when the foreign key changes. The composite keys are raf_id and ticket_no since the ticket no should not exist multiple times in same raffle Is this possible? |------------|-----------|---------| | raf_id | ticket_no | user_id | |------------|-----------|---------| | 1 | 4000 | 1 | | 1 | 4001 | 1 | | 1 | 4002 | 2 | | 1 | 4003 | 3 | | 2 | 4000 | 4 | | 2 | 4001 | 4 | | 2 | 4002 | 5 | | 2 | 4003 | 1 | |------------|-----------|---------|