Discovering SQL: A Hands-On Guide for Beginners

AUTO-INCREMENTED VALUES

Almost every table in the Library database has a primary key of a numeric data type, and the INSERT statements include the actual values for each record. The purpose of the numbers is to enforce referential integrity and possibly to keep track of the records because the numbers increase with every new book added. These values are not used anywhere else; in fact, it is considered to be a best practice to use meaningless (in context of the table's data) unique values for the primary key columns, as have pointed out in Chapter 3. Keeping track of these numbers to know exactly what to insert next is a nuisance in small single-user databases and can be a major headache for large multiuser systems.

How can you find out what the next number will be? You can query the table for the maximal number; this is how it used to be in the days of yore (Microsoft SQL Server syntax):

SELECT @next_value = MAX(book_id)+1 FROM books;

INSERT INTO books (bk_id, bk_title)
    VALUES (@next_value, ‘NEXT BOOK IN SEQUENCE’)

There are several major problems with this approach, though. The first problem arises in multiuser environments. However brief the time it takes for the query to get executed, there is no guarantee that somebody else's query would not grab the same value and thus try to insert the same value as you. This can be addressed with locking down the table in a transaction (see Chapter 10 for more information on transactions), which would negatively affect performance. ...