At 10:37 AM -0400 8/29/01, Ferrara, Joseph C wrote:
>I want to create a table with two Primary Keys. The first key is a category
>field, and the second is auto-incremented. Example: Key 1=Chapter name, Key
>2 =section number. For example, (Chapter1, 1) (Chapter1, 2) (Chapter1, 3)
>then with a new Cheaper, I want to restart the auto-incremented field back
>to 1 (Chapter2, 1) (Chapter2, 2). How do I get the auto-incrementer to
>restart with each new chapter?
Create a single PRIMARY KEY that consists of the two columns chapter and
section, and make section an AUTO_INCREMENT column. When you insert
rows, set chapter to the proper chapter number and section to NULL.
This will cause MySQL to generate independent sequences for each chapter.
(In essence, you have multiple sequences within the same table.)
This requires that you have MySQL 3.22.25 or later. That's when this
feature was introduced.
Paul DuBois, paul@stripped