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 ...
Get MySQL and mSQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.