Creating and Manipulating Tables - MariaDB Crash Course - page 176

Tip
Primary Keys and NULL Values Back in Chapter 1, you learned that primary keys are
columns whose values uniquely identify every row in a table. Only columns that do not
allow NULL values can be used in primary keys. Columns that allow no value at all can-
not be used as unique identifiers.
Using AUTO_INCREMENT
Let's take a look at the customers and orders tables again. Customers in the
customers table are uniquely identified by column cust_id , a unique num-
ber for each and every customer. Similarly, orders in the orders table each
have a unique order number that is stored in column order_num .
These numbers have no special significance, other than the fact that they are
unique. When a new customer or order is added, a new customer ID or order
number is needed. The numbers can be anything, so long as they are unique.
Obviously, the simplest number to use would be whatever comes next, what-
ever is one higher than the current highest number. For example, if the high-
est cust_id is 10005 , the next customer inserted into the table could have a
cust_id of 10006 .
Simple, right? Well, not really. How would you determine the next number
to be used? You could, of course, use a SELECT statement to get the highest
number (using the Max() function introduced in Chapter 12, “Summarizing
Data”) and then add 1 to it. But that would not be safe (you'd need to find a
way to ensure that no one else inserted a row in between the time that you
performed the SELECT and the INSERT , a legitimate possibility in multiuser
applications). Nor would it be efficient (performing additional SQL operations
is never ideal).
And that's where AUTO_INCREMENT comes in. Look at the following line (part
of the CREATE TABLE statement used to create the customers table):
cust_id int NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT tells MariaDB that this column is to be automatically
incremented each time a row is added. Each time an INSERT operation is per-
formed MariaDB automatically increments (and thus AUTO_INCREMENT ) the
column, assigning it the next available value. This way each row is assigned a
unique cust_id that is then used as the primary key value.
Only one AUTO_INCREMENT column is allowed per table, and it must be
indexed (for example, by making it a primary key).