Multiple Increments In 1 table

[tassflint]

Hello all,

I am needing to create a table like this:

id - main auto increment id for each row
prefix - holds 3 different 3 letter combinations - supplied
prefixid - autoincrement based on prefix

So the table would look like this after a few entries

id prefix prefixid
1 aaa 1
2 aaa 2
3 bbb 1
4 ccc 1
5 ccc 2
6 aaa 3
7 bbb 2

I have set the primary key to be the prefix, prefixid combination. I want the first id to be the actual table index. I am unable to set both up to auto increment. Is there a way to do this so that I can just do inserts, and not have to go in and check what is the last id used and add 1 before I do an insert?



Thanks in advance,

Brian

 

[SQLSister]

No automatic way, you will have to write code to this. I suggest putting it in a trigger.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief