auto increment data type for order number

philosophia

hi all

i've been trying to add a new variable to a table - this variable will be 'order_number'. i initially tried INT UNSIGNED NOT NULL AUTO INCREMENT UNIQUE

when i try to alter the table i get the error

Incorrect table definition; ; There can only be one auto column and it must be defined as a key

--i am pretty sure i'm getting this error because the primary key to this db is 'userid' - which is also auto increment.

i would like to auto number order numbers. is there any way around this?

one thing to note: the app i am writing is such that nobody will be ordering more than two items, and they can only pay once for each item. so i was thinking of changing the data type from int to varchar - then making order number equal to userid+itema or userid+itemb.

are there any disadvantages to using varchar for order numbers? i'd rather use int but there doesn't seem to be a way around it in this case.

any advice you guys can give would be really appreciated

thanks
philosophia

klansman

about the table: your answer is there. you cannot have more than one auto-increment column in a table, and it must be defined as a key!

the way around it is to update the order numbers manually from the php scripts...

maybe you can normalize your table... you could create a new table with columns like:

userid | orderid | order_details...

you could easily count how many orders each user has... about the single payment per order, you can put orderid as unique auto-increment in this table, so that each order has it's own unique orderid. then you can create another table with payments, of course connected to this one, allowing you to easily verify the payments...

hope it helped...

[]

philosophia

Originally posted by klansman
hi

about the table: your answer is there. you cannot have more than one auto-increment column in a table, and it must be defined as a key!

the way around it is to update the order numbers manually from the php scripts...

maybe you can normalize your table... you could create a new table with columns like:

userid | orderid | order_details...

you could easily count how many orders each user has... about the single payment per order, you can put orderid as unique auto-increment in this table, so that each order has it's own unique orderid. then you can create another table with payments, of course connected to this one, allowing you to easily verify the payments...

hope it helped...

[]

yes, thank you
i get the idea

klansman

maybe you can read more about it, by looking for "database normalization" techniques. i know that phpbuilder.com has a couple or articles on this.

[]