Creating and Manipulating Tables - MariaDB Crash Course - page 173

Each column definition starts with the column name (which must be unique
within the table), followed by the column's datatype. (Refer to Chapter 1,
“Understanding SQL,” for an explanation of datatypes. In addition, Appendix
C, “MariaDB Datatypes,” lists the datatypes supported by MariaDB.) The
table's primary key may be specified at table creation time using the PRIMARY
KEY keywords; here, column cust_id is specified as the primary key column.
The entire statement is terminated with a semicolon after the closing parenthe-
sis. (Ignore the ENGINE=Aria and AUTO_INCREMENT statements for now; we
come back to that later.)
Tip
Statement Formatting As you will recall, whitespace is ignored in SQL statements.
Statements can be typed on one long line or broken up over many lines. It makes no
difference at all. This enables you to format your SQL as best suits you. The preceding
CREATE TABLE statement is a good example of SQL statement formatting—the code
is specified over multiple lines, with the column definitions indented for easier reading
and editing. Formatting your SQL this way is entirely optional, but highly recommended.
Tip
Handling Existing Tables When you create a new table, the table name specified must
not exist or you'll generate an error. To prevent accidental overwriting, SQL requires that
you first manually remove a table (see later sections for details) and then re-create it,
rather than just overwriting it.
If you want to create a table only if it does not already exist, specify IF NOT EXISTS
after the table name. This does not check to see that the schema of the existing table
matches the one you are about to create. It simply checks to see whether the table
name exists, and only proceeds with table creation if it does not.
Working with NULL Values
Back in Chapter 6, “Filtering Data,” you learned that NULL values are no val-
ues or the lack of a value. A column that allows NULL values also allows rows
to be inserted with no value at all in that column. A column that does not
allow NULL values does not accept rows with no value—in other words, that
column will always be required when rows are inserted or updated.
Every table column is either a NULL column or a NOT NULL column, and that
state is specified in the table definition at creation time. Take a look at the fol-
lowing example: