Creating and Manipulating Tables - MariaDB Crash Course - page 175

Caution
Understanding NULL Don't confuse NULL values with empty strings. A NULL value
is the lack of a value; it is not an empty string. If you were to specify '' (two single
quotes with nothing in between them), that would be allowed in a NOT NULL column.
An empty string is a valid value; it is not no value. NULL values are specified with the
keyword NULL , not with an empty string.
Primary Keys Revisited
As already explained, primary key values must be unique. That is, every row in
a table must have a unique primary key value. If a single column is used for the
primary key, it must be unique; if multiple columns are used, the combination
of them must be unique.
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 col-
umn 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).