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).
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.
>
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
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
