MySQL and mSQL

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.

MySQL Sequences

When you create a table in MySQL, you can specify at most one column as ...