Primary Key question

[thesti]

hi,

is it possible to have an auto increment primary key like 'SP001' then 'SP002'. so the next time i insert new row to the table, i don't have to specify the value for the primary key as it will be 'SP003'.

if it's not possible, what could be a good workaround for this. any advice is appreciated.

Thank you,
SC

[r035198x]

Three possibilities.
1) Simply store the IDs as integers auto incrementing but prepend the prefix when presenting the data to the user in the presentation layer.
2.) Use another column called say, reference number which you populate with the required format. (I don't like this approach).
3.) Do a query on the table to find that the last number was and then generate the next key yourself.

I'd simply use 1.

[mwasif]

Use an AUTO_INCREMENT column with ZEROFILL attribute. This will create the numbers like 001, 002 and so on. Then you can concatenate this number with SP when displaying the data.

[@r035198x - Didn't refresh the page before posting reply]

[r035198x]

..

[@r035198x - Didn't refresh the page before posting reply]

No problem. I do it all the time.

[thesti]

hi,

thank you for your replies. i think i'll use 1. i never thought that. thanks

[thesti]

hello,

one more question, when i create the table and set the primary key field with zerofill, there seems to be 9 leading zeroes. while actually i only need a 3 digits fixed length int. should the manipulation to get the last three digit be done in the application code by using substr for example?

Thank you.

[Atli]

You can specify the max *length* of the integer by adding a (n) to the INT keyword.
[code=sql]CREATE TABLE mytbl ( MyID Int(3) Zerofill Primary Key )[/code]