I am building an e-commerce site, and for my Order table I need primary key as well as an "OrderNo".

For the primary key, I could just create an "ID" field and set it to Auto-Increment.

If I take that approach, I have a solid PK, but that wouldn't work for my "OrderNo" because...

1.) An "OrderNo" should be a fixed width value,

2.) It would be awkward to have OrderNo = 1, OrderNo =2,..., OrderNo = 73129

Is there some way to combine an "ID" field and an "OrderNo" field into one, but benefit from letting MySQL generate and increment the value for me, while not going 1, 2, 3...??



TomTees

nope, sorry

you really only need one unique column, not two

if you're going to assign your own orderno, you won't need an auto_increment as well

I understand that, but that isn't what I was asking...

I don't want order numbers that go 1, 2, 3,...

They should be fixed-width (e.g. 853021, 853022, 853023,...)

And they should start at some number high enough so it doesn't look like we just started our business?!

If I have an ID and use Auto-Increment, can I...

1.) Start the Auto-Increment at a number higher than 1?

2.) Pad the Auto-Increment Number so it is fixed width?


If I cannot do those things, then what is the best way to generate an OrderNo so that it is reliable and there aren't any issues with concurrent users creating duplicate OrderNo's. (I'd guess that I need to use "Transaction Processing" in MySQL, right?)


TomTees

yes, an auto_increment can do both those things, all you have to do is insert the first row in the table with a sufficiently high id number, e.g. 857937, and they will increment from there


the best way would be an auto_increment, and you do not need transaction processing to use it

Personaly I think that OrderID can be a calculated field. I do not preffer the ideea of an iddentity...

I do not know the size and architecture of your application... for complex applications with different distributed mysql databases... may be a bit confusing... or a big hard to setup (from administrative point of view)... to use iddentity.

So, I think that you must can consider building a system, a formula for your order ID... like:
LOCATION-OPERATOR_ID-ID... or anything else.