List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:August 29 2001 5:25pm
Subject:Re: Primary Key pairs and auto-incrementing
View as plain text  
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
Primary Key pairs and auto-incrementingJoseph C Ferrara29 Aug
  • RE: Primary Key pairs and auto-incrementingChris Bolt29 Aug
  • Re: Primary Key pairs and auto-incrementingPaul DuBois29 Aug
    • Re: Primary Key pairs and auto-incrementingCarl Troein30 Aug