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

database-designtable

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


# Automatic?

Unknown. Most likely, not. (as in: no out-of-the-box database features)

# Manual?

Yes.

You need to keep track of "last number used" in a table. eg

create table raf (
raf_id        int, -- identity/autoincrement
last_number   int default 4000 not null,
constraint raf_pk primary key (raf_id)
);


From there, your process needs to go like this:

1. grab a lock for RAF_ID
2. "calculate" the next number.
3. Update the RAF table.
4. Use that value in the other table
5. COMMIT the data
6. Release the lock.

Depending on your RDBMS, some steps can be combined into a single step. The HOWTO do these things (eg Best practice for "grab a lock") can also be RDBMS dependent.

# Notes

The "grab a lock" step will serialize your code. This is bad for databases. This requirement/process will limit how fast you can add new rows. I suggest you look at alternative methods (eg a VIEW that uses RANK() ).

Can this be implemented in a TRIGGER? Maybe.