MariaDB Crash Course - Ben Forta [63]
The CREATE TABLE examples seen thus far use a single column as the primary key. The primary key is thus defined using a statement such as
PRIMARY KEY (vend_id)
To create a primary key made up of multiple columns, simply specify the column names as a comma-delimited list, as seen in this example:
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=Aria;
The orderitems table contains the order specifics for each order in the orders table. There may be multiple items per order, but each order will only ever have one first item, one second item, and so on. As such, the combination of order number (column order_num) and order item (column order_item) is unique, and thus suitable to be the primary key, which is defined as
PRIMARY KEY (order_num, order_item)
Primary keys may be defined at table creation time (as seen here) or after table creation (as discussed later in this chapter).
* * *
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 cannot 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 number 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, whatever is one higher than the current highest number. For example, if the highest 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 performed 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).
* * *
Note: Overriding AUTO_INCREMENT
Need to use a specific value if a column is designated as AUTO_INCREMENT? You can—simply specify a value in the INSERT statement, and as long as it is unique (has not been used yet) that value will be used instead of an automatically generated one. Subsequent incrementing will start using the value manually inserted. (See the table population scripts in Appendix B, “The Example Tables” for examples of this.)
* * *
* * *
Tip: Determining the AUTO_INCREMENT Value
One downside of having MariaDB generate (via auto increment) primary keys