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: