MariaDB Crash Course - Ben Forta [62]
* * *
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 values 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 following example:
Input
CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_id int NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=Aria;
Analysis
This statement creates the orders table used throughout this book. orders contains three columns: order number, order date, and the customer ID. All three columns are required, and so each contains the keyword NOT NULL. This prevents the insertion of columns with no value. If someone tries to insert no value, an error will be returned, and the insertion will fail.
This next example creates a table with a mixture of NULL and NOT NULL columns:
Input
CREATE TABLE vendors
(
vend_id int NOT NULL AUTO_INCREMENT,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL ,
PRIMARY KEY (vend_id)
) ENGINE=Aria;
Analysis
This statement creates the vendors table used throughout this book. The vendor ID and vendor name columns are both required, and are, therefore, specified as NOT NULL. The five remaining columns all allow NULL values, and so NOT NULL is not specified. NULL is the default setting, so if NOT NULL is not specified, NULL is assumed.
* * *
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.