Sequence Generation
The best kind of primary key is one that has absolutely no meaning in the database except to act as a primary key. Primary keys are the tools used to identify rows uniquely in a relational database. When you use information such as a username or an email address as a primary key, you are in effect saying that the username or email address is somehow an intrinsic part of who that person is. If that person ever changes his username or email address, you will have to go to great lengths to ensure the integrity of the data in the database. Consequently, it is a better design principle to use meaningless numbers as primary keys.
To achieve this, simply 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. To successfully manage this primary key
sequencing, you need some way to guarantee that a number can be read
and incremented by only one client at a time. You accomplish this
task by making the primary key field
AUTO_INCREMENT
.
When you create a
table in
MySQL, you can specify at most one
column as
AUTO_INCREMENT
. When you do this, you can have
this column automatically insert the highest current value plus 1 for
that column when you insert a row and specify NULL
or 0 for that row’s value. The
AUTO_INCREMENT
columns must be indexed. The
following command creates the cities table with an
AUTO_INCREMENT id
field: ...
Get Managing & Using MySQL, 2nd Edition 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.