The final part of a column definition (following the datatype) can include optional modifiers. These options are described in the following list. Note that many of them apply only to certain column types.

• UNSIGNED applies to numeric datatypes and causes negative values to be disallowed. If you attempt to store a negative value in an UNSIGNED column, MySQL stores zero instead.

• ZEROFILL applies to integer numeric column types. It causes retrieved values to be left-padded with leading zeros up to the column's display width. For example, if you store the values 0, 14, and 1234 in a column that's defined as INT(5) ZEROFILL, MySQL displays them as 00000, 00014, and 01234 when you retrieve them.

  Using the ZEROFILL option for a column causes it to be UNSIGNED as well.

• AUTO_INCREMENT applies to integer numeric column types. It's used to generate sequences of successive unique values. Defining a column with AUTO_INCREMENT causes a special behavior: When you insert NULL into the column, MySQL generates the next value in the sequence automatically and stores that in the column instead. Use of AUTO_INCREMENT carries with it other requirements: There may be only one AUTO_INCREMENT column per table, the column must be indexed, and the column must be defined as NOT NULL. Section 4.10.5, "Using the AUTO_INCREMENT Column Option," provides specific details on the use of AUTO_INCREMENT columns.

• BINARY applies to the CHAR and VARCHAR datatypes. CHAR and VARCHAR columns are nonbinary by default; adding BINARY to the definition causes column values to be treated as binary strings.

  Beginning with MySQL 4.1, BINARY may also be applied to ENUM and SET columns to cause case-sensitive treatment of column values.

• NULL and NOT NULL apply to all column types. They indicate whether or not a column can contain NULL values. If you specify neither option, the default is NULL, which allows NULL values in the column.

• DEFAULT value provides the column with a default value to be used when you create a new record but don't explicitly specify a value for the column (for example, when you execute an INSERT statement that doesn't provide values for all columns in the table). This attribute applies to all column types except BLOB and TEXT. A default value must be a constant; it cannot be an expression whose value is calculated at record-creation time.

  If you don't specify a DEFAULT value for a column, MySQL chooses a default for you. The value is NULL if the column may contain NULL; otherwise, the value depends on the column type:

  • For numeric columns, the default is zero.

  • For string columns other than ENUM, the default is the empty string. For ENUM columns, the default is the first enumeration member.

  • For temporal columns, the default value is the "zero" value for the datatype, represented in whatever format is appropriate to the column type (for example, '0000-00-00 'for DATE and '00:00:00 'for TIME).

  The exceptions to the preceding are the first TIMESTAMP column in a table and integer columns that have the AUTO_INCREMENT attribute. For such columns, MySQL uses a default value of the current date and time, and the next sequence number, respectively. Furthermore, if you supply a DEFAULT option for these column types, MySQL ignores it or produces an error.

  It's an error to specify a default value of NULL for a NOT NULL column.

• PRIMARY KEY and UNIQUE may be given at the end of a column definition, for all datatypes except BLOB and TEXT. They cause the creation of a PRIMARY KEY or UNIQUE index for the column. Adding either of these options to a column definition is the same as defining the index in a separate clause. For example, the following table definitions are equivalent:

  CREATE TABLE t (i INT NOT NULL PRIMARY KEY);
  
  CREATE TABLE t (i INT NOT NULL, PRIMARY KEY (i));

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.