Extra Column Characteristics

When declaring the type for a column, you first select the broad typenumber, text, or datethen select a more specific type within these. From there, columns can also be defined with other characteristics. A special characteristic, AUTO_INCREMENT, is discussed in the sidebar, and more common ones are: UNSIGNED, ZEROFILL, NOT NULL, and DEFAULT.

For starters, the number types can be set as UNSIGNED. This means that the column can only store non-negative values. This has a secondary effect with integers of doubling the range of positive numbers that can be stored (the same is not true for real numbers). Number types can also be defined as ZEROFILL, which means that any extra room will be padded on the left with zeros (ZEROFILLs are also automatically UNSIGNED).

Any column type can be defined as NOT NULL. The NULL value in databases is the equivalent of saying that the field has no known value. (This may differ from how you've thought of NULL in other contexts.) Ideally, every record in a database should have a value, but that is rarely the case in practicality. To enforce this limitation on a field, you add the NOT NULL description to its column type.

The AUTO_INCREMENT Designation

One attribute a numeric column can have is AUTO_INCREMENT. When you define a field with this property, you are effectively telling MySQL to set the value of this column to the next logical value in the series. This attribute is normally applied to a table's primary key, like the Invoice Number or Client ID.

Once you've defined your column as such, if you do not set a value for that column when adding a record, the next highest integer will be used. So the first Invoice Number will be 1, the second will be 2, the third will be 3, and so on. MySQL will automatically handle this for you.

Some find it concerning that if you were to later delete Invoice Number 3, there would be a "gap" in the sequence. This is perfectly fine! Your primary key is an arbitrary value. There's no harm in having the Invoice Numbers go 1, 2, 4, 5, 8, .... In fact, the harm would be in trying to "fix" that situation.

When creating a table, you can also specify a default value for any column (except for types TEXT and BLOB). In cases where a large portion of the records will have the same contents, presetting a default will save you from having to specify a value when inserting new rows, unless that value is different from the norm. One example might be

gender ENUM('M','F') DEFAULT 'F'

Table 4.5 lists all of the columns of the accounting database, along with their full definitions, after taking the following steps.

Table 4.5. Extra characteristics have been added to each column as appropriate.

Accounting Database, Modified
Column Name	Table	Column Type
Invoice Number	Invoices	SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT
Client ID	Invoices	SMALLINT(3) UNSIGNED NOT NULL
Invoice Date	Invoices	TIMESTAMP NOT NULL
Invoice Amount	Invoices	DECIMAL(10,2) UNSIGNED NOT NULL
Invoice Description	Invoices	TINYTEXT NOT NULL
Date Invoice Paid	Invoices	DATE
Client ID	Clients	SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT
Client Name	Clients	VARCHAR(40) NOT NULL
Client Street Address	Clients	VARCHAR(80) NOT NULL
Client City	Clients	VARCHAR(30) NOT NULL
Client State	Clients	CHAR(2) NOT NULL
Client Zip	Clients	MEDIUMINT(5) UNSIGNED ZEROFILL NOT NULL
Client Phone	Clients	VARCHAR(14)
Contact Name	Clients	VARCHAR(40)
Contact Email Address	Clients	VARCHAR(60)
Expense ID	Expenses	SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT
Expense Category ID	Expenses	TINYINT(3) UNSIGNED NOT NULL
Expense Amount	Expenses	DECIMAL(10,2) UNSIGNED NOT NULL
Expense Description	Expenses	TINYTEXT NOT NULL
Expense Date	Expenses	TIMESTAMP NOT NULL
Expense Category ID	Expense Categories	TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT
Expense Category	Expense Categories	VARCHAR(30) NOT NULL

To customize your columns:

1.	1. Identify any column that cannot have a NULL value. This is the most important of the extra designations. Any column defined as NOT NULL must always be given a value. As you'll see when you go to add records to a table, failure to give a value to a NOT NULL column results in an error. As a rule of thumb, you should define columns as NOT NULL whenever possible. They'll take up slightly less space this way and offer better performance.
2.	2. Identify any number types that should be UNSIGNED. This is an easy step to take. If a number must be positive, like the price or quantity of something ordered, it should be UNSIGNED. If it may be negative, like a temperature or a bank balance (d'oh!), do not flag it as such.
3.	3. Identify any number types that should be ZEROFILL. The ZEROFILL designation is much less used than UNSIGNED but is necessary in some instances. For example, with the zip code column, some zip codes begin with a 0, like 02101. If you were to store this in a non-ZEROFILL integer column, it would be stored as 2101 (because the initial 0 has no meaning to an integer). By defining that column as MEDIUMINT(5) UNSIGNED ZEROFILL, the stored zip codes will retain their initial 0.
4.	4. Identify any column that should have a default value. This step is mostly a matter of personal preference.

Tips

• Primary keys can never contain NULL values, in accordance with proper database design and with how MySQL functions.

• If an ENUM column is set as NOT NULL, the default value will automatically be the first of the possible allowed values.

• Just to clarify what NULL is, understand that NULL has no value and differs from even the number zero, an empty string ("), or a space (' '), which are all known values.

• You should be forewarned that MySQL has an odd "feature" when it comes to UNSIGNED integers. If you perform subtraction with at least one UNSIGNED integer, the result will always be UNSIGNED. So an UNSIGNED column with a value of 2 minus a signed column with a value of 10 will not be 8.