Using AUTO_INCREMENT To Set Up a Sequence Column

11.2.1 Problem

You want to include a sequence column in a table.

11.2.2 Solution

Use an AUTO_INCREMENT column.

11.2.3 Discussion

This section provides the basic background on how AUTO_INCREMENT columns work, beginning with a short example that demonstrates the sequence-generation mechanism. The illustration centers around a bug-collection scenario: your son (eight-year-old Junior) is assigned the task of collecting insects for a class project at school. For each insect, Junior is to record its name ("ant," "bee," and so forth), and its date and location of collection. You have long expounded the benefits of MySQL for record-keeping to Junior since his early days, so upon your arrival home from work that day, he immediately announces the necessity of completing this project and then, looking you straight in the eye, declares that it's clearly a task for which MySQL is well-suited. Who are you to argue? So the two of you get to work. Junior already collected some specimens after school while waiting for you to come home and has recorded the following information in his notebook:

Name

Date

Origin

millipede

2001-09-10

driveway

housefly

2001-09-10

kitchen

grasshopper

2001-09-10

front yard

stink bug

2001-09-10

front yard

cabbage butterfly

2001-09-10

garden

ant

2001-09-10

back yard

ant

2001-09-10

back yard

millbug

2001-09-10

under rock

Looking over Junior's notes, you're pleased to see that even at his tender age he has learned to write dates in ISO format. However, you also notice that he's collected a millipede and a millbug, neither of which actually are insects. You decide to let this pass for the moment; Junior forgot to bring home the written instructions for the project, so at this point it's unclear whether or not these specimens are acceptable.

As you consider how to create a table to store this information, it's apparent that you need at least name, date, and origin columns corresponding to the types of information Junior is required to record:

CREATE TABLE insect
(
 name VARCHAR(30) NOT NULL, # type of insect
 date DATE NOT NULL, # date collected
 origin VARCHAR(30) NOT NULL # where collected
);

However, those columns may not be enough to make the table easy to use. Note that the records collected thus far are not uniqueboth ants were collected at the same time and place. If you put the information into an insect table that has the preceding structure, neither ant record can be referred to individually, because there's nothing to distinguish them from one another. Unique IDs would be helpful to make the records distinct and to provide values that make each record easy to refer to. An AUTO_INCREMENT column is good for this purpose, so a better insect table has a structure like this:

CREATE TABLE insect
(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (id),
 name VARCHAR(30) NOT NULL, # type of insect
 date DATE NOT NULL, # date collected
 origin VARCHAR(30) NOT NULL # where collected
);

Go ahead and create the insect table using this second definition. Later, in Recipe 11.4, we'll discuss the specifics of why the id column is declared the way it is.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net