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

Best Answer

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:

grab a lock for RAF_ID
"calculate" the next number.
Update the RAF table.
Use that value in the other table
COMMIT the data
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.

Related Solutions

Mysql – Auto-increment key and Foreign Key as composite for relational database

You don't need composite keys to enforce your referential integrity in your case. The reason is that you have a pretty straight-forward three tier hierarchy:

PATIENT 
   +
   |
   ^
 TEST 
   +
   |
   ^
SAMPLE

Your SAMPLE table just needs a simple foreign key to your TEST table and your TEST table just needs a simple foreign key to your PATIENT table.

This works because each sample record needs a test record and each test needs a patient. If you delete a patient, their tests must be cascade deleted. If you delete a test its samples must be cascade deleted. Therefore you don't need patient_id in the TEST table. All you need is to declare cascade deletion on your foreign keys, like so...

...
CONSTRAINT 'FK_TEST__PATIENT' FOREIGN KEY ('patient_id')
      REFERENCES 'PATIENT' ('patient_id') ON DELETE CASCADE
...
CONSTRAINT 'FK_SAMPLE__TEST' FOREIGN KEY ('test_id')
      REFERENCES 'TEST' ('test_id') ON DELETE CASCADE
...

Sql-server – Composite Key for Gizmo Tracking

as it will have three table

one table for Owner, Identity Auto-Increment by 1 will give OwnerId
one table for gizmos, Identity Auto-Increment by 1 will give gizmosId
One table for mapping the many to many relationship as

OwnerId - FK gizmosId - FK and Other columns

Now as the combination of OwnerId and gizmosId will make a row unique you can create primary key constraints that will enforce Uniqueness as well.

ALTER TABLE schemaName.TableName
ADD CONSTRAINT PK_Relation PRIMARY KEY CLUSTERED(OwnerId , gizmosId);