List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 12 2001 5:14am
Subject:RE: Auto Increment
View as plain text  
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:chris.lists@stripped]
>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, paul@stripped
Thread
Auto IncrementRich Duzenbury12 Jun
  • RE: Auto IncrementChris Bolt12 Jun
RE: Auto IncrementNoel Clarkson12 Jun
  • RE: Auto IncrementPaul DuBois12 Jun