Auto Increment for non integer ?

[saleemMSMS]

hi i have a table called invoice. the primary key supposed to be not only a number, but also a string. i thought of making the primary key as follows. INV001, INV002 etc. is there a way to auto increment such string values ? if yes how to ? if no, can anyone suggest me a way of having a sequential yet string attached primary key ?
thanx

 

[pbaldy]

Search here on DMax and you should find numerous threads. If the text part is static like that, I'd just store a number and add the text for display to the user.

 

[giovaanni]

I've handled this by having a separate table for "starting" numbers. Then just write code everytime you add a new invoice (through clicking the add button on your form) to go to this table, grab the string, add 1 to it, use this for your key, then replace the string in the starting numbers table with the new value.

 

[Atomic Shrimp]

If it's important that your invoice numbers run in sequence without gaps then you'll have to write a function to assign the next one - autonumbers can quite easily develop gaps in their sequence.

And as others have said, I'd only store the part that changes - the static text prefix is easy to concatenate on whenever you need it.

I would also give the table an autonumber ID column and use this to relate to any other tables such as invoice detail, payments, etc

- If you use a meaningful field to perform this relation, you will run into trouble maintaining existing relationships if the numbering format of your invoices ever needs a revamp. If you use a hidden autonumber to perform the relationship, it just carries on as normal even if you do something quite exotic to your invoice numbering.