At 1:31 PM +1000 6/12/01, Noel Clarkson wrote:
>I don't know about MySQL 3.22.32 but in 3.23.27 if you create a table with
>a primary key of two fields, one of which is an auto increment field then
>you will get the behaviour you are looking for (ie the auto inc will start
>at 1 for each different value in the other field). I don't have 3.22.x
>installed and am not sure if this has been a change in later versions, but
>you could give it a go and see what happens (there is an example of what I
>did below).
It should work. This change was made in 3.22.25.
>
>cheers,
>
>noel
>
>
>mysql> create table testme(a varchar(3) not null, b integer auto_increment
>not n
>ull, PRIMARY KEY(a,b));
>mysql> insert into testme values("hee", null);
>mysql> select * from testme
>mysql> insert into testme values("hee", null);
>mysql> select * from testme;
>+-----+---+
>| a | b |
>+-----+---+
>| hee | 1 |
>| hee | 2 |
>+-----+---+
>2 rows in set (0.00 sec)
>
>mysql> insert into testme values("ha", null);
>mysql> select * from testme;
>+-----+---+
>| a | b |
>+-----+---+
>| ha | 1 |
>| hee | 1 |
>| hee | 2 |
>+-----+---+
>3 rows in set (0.01 sec)
>
>On Tuesday, June 12, 2001 11:33 AM, Chris Bolt [SMTP:[EMAIL PROTECTED]]
>wrote:
>Ver 8.0, Distrib 3.22.32
>
>> > 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.
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
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
