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 ( ZEROFILL s 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`

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

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 , like 02101 . If you were to store this in a non- `ZEROFILL` integer column, it would be stored as 2101 (because the initial has no meaning to an integer). By defining that column as `MEDIUMINT(5) UNSIGNED ZEROFILL` , the stored zip codes will retain their initial .
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.