6.5. Sequences and Auto-Incrementing
The best kind of primary key is one that has absolutely no meaning in the database except to act as a primary key. The best way to achieve this is to make a numeric primary key that increments every time you insert a new row. Looking at the cities table shown earlier, the first city you insert would have an id of 1, the second 2, the third 3, and so on. In order to successfully manage this sequencing of a primary key, you need some way to guarantee that a number can be read and incremented by one and only one client at a time. Under transactional databases, you could create a table called sequence that has a number representing the next id. When you need to insert a new row, you would read that table and insert a new number one more than the one you read. You must be assured that no one else will read from that table before you insert a new value, however, in order for that scheme to work. Otherwise, two clients could read the same value and attempt to use it as a primary key value in the same table.
Neither MySQL nor mSQL support transactions, so the previously identified mechanism cannot be used for generating unique ID numbers. The MySQL command LOCK TABLE is cumbersome for this task. However, both engines support their own variant of a concept called a sequence, which enables you to generate unique ID numbers without worrying about those transactional issues.
6.5.1. MySQL Sequences
When you create a table in MySQL, you can specify at most one column as being AUTO_INCREMENT. When you do this, you can automatically have this column insert the highest current value for that column + 1 when you insert a row and specify NULL or for that row's value. The AUTO_INCREMENT row must be indexed. The following command creates the cities table with the id field being AUTO_INCREMENT:
CREATE TABLE cities (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), pop MEDIUMINT, founded DATE)
The first time you insert a row, the id field for your first row will be 1 so long as you use NULL or for that field in the INSERT statement. For example, this command takes advantage of the AUTO_INCREMENT feature:
INSERT INTO cities (id, name, pop) VALUES (NULL, 'Houston', 3000000)
If no other values are in that table when you issue this command, MySQL will set this field to 1, not NULL (remember, it cannot be NULL). If other values are present in the table, the value inserted will be one greater than the largest current value for id.
Another way to implement sequences is by referring to the value returned by the LAST_INSERT_ID function:
UPDATE table SET id=LAST_INSERT_ID (id+1);
6.5.2. mSQL Sequences
CREATE SEQUENCE ON table_name [VALUE start STEP incr]
The start value is the number to start with. The incr value is the amount to increment on each access. By default, a sequence starts with 1 and increments 1 at a time. For example:
CREATE SEQUENCE ON mytable VALUE 100 STEP 5
This command creates a sequence on the mytable table whose first value will be 100 and will increase by 5 each time some accesses the sequence. The second value under this scheme would therefore be 105.
In order to access a sequence, you need to select a special column called _seq from the table:
SELECT _seq FROM table_name
Copyright © 2001 O'Reilly & Associates. All rights reserved.