> I have a customer file, keyed by an auto-increment customer
> number. Customers can have orders. The order file is keyed by an
> auto-increment order sequence number, which works fine, but is not
> convenient. Rather, I would like the order sequence number to start at 1
> for each customer, and have two keys on the order file, customer id,
> followed by order sequence. I would like the database to enforce
> uniqueness on this key pair.
>
> I don't think the built in auto-increment field can support this kind of
> mechanism, so - What is a safe, efficient way to find the
> customers highest
> order number, increment it by one, and write the new order
> record? I need
> to be absolutely sure that if ten people enter a new order all
> for the same
> customer at (nearly) the same time, they all get a unique order number.
Create a single unique index on both columns (CREATE UNIQUE INDEX
ordernumber ON table (customerid, orderid);) and use LOCK TABLES when
creating the order id.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
