Additional Column Modifiers
MySQL has several key words that modify how a column acts. For example, you have already learned about
the AUTO_INCREMENT, UNSIGNED, and ZEROFILL modifiers and how they affect the column in which they
are used. Some modifiers only apply to certain type columns. Look at Table 7.4 to see what modifiers are
available for what type.
Table 7.4 Column Modifiers
Modifier Name Applicable
TypesAUTO_INCREMENT (^) All INT
Types
BINARY CHAR,
VARCHAR
DEFAULT (^) All, except
BLOB,
TEXT
NOT NULL
All Types
NULL
All Types
PRIMARY KEY (^) All Types
UNIQUE
All Types
UNSIGNED
Numeric
Types
ZEROFILL
Numeric
Types
The BINARY modifier causes the values stored in these types to treated as binary strings, making them
case sensitive. When you sort or compare these strings, they will also take case into consideration. By
default, VARCHAR and CHAR types are not stored as binary.
The DEFAULT modifier allows you to specify the value of a column if one does not exist. The MySQL
default value is NULL for all types except ENUM. MySQL uses the first value of the enumerated list as
the default. For SET types, MySQL uses the empty string for the default. To specify a DEFAULT value,
use the following syntax:
CREATE TABLE Test(State char(2) NOT NULL DEFAULT "KY")
This will make all records that have a NULL or an empty string value in the State column have the
default value, which is "KY".
The NULL and NOT NULL modifiers specify whether a column must have some sort of value in it. For
example, if a column is defined as NOT NULL, a value must be placed in that column. Remember that
NULL is absolutely no value whatsoever. An empty string (" "), even though it looks like it is nothing, is
NOT NULL. Using NULL and NOT NULL can force required constraints on the data that is being stored.
Review Day 3, "Designing Your First Database," for more information.
The PRIMARY KEY is actually an index that must contain unique values. It cannot be NULL. Every table
should have a key, and MySQL allows you to easily create an index by declaring this key as your
PRIMARY KEY.
Note Even though MySQL does not require you to have them, keys are an essential
tool for database performance. Keys are covered extensively on Day 11, "MySQL
Table Locks and Assorted Keys."
The UNIQUE modifier enforces the rule that all data within the declared column must be unique. If you
try to insert a value that is not unique, an error will be generated.
Summary
As you can see, there are many different kinds of data types. Each type has its own characteristics. It is up
to you, the database designer, to associate the correct types with the type of data that you are going to store.
This is not always an easy job. But remember this, if you make a wrong choice, you can always change the
type with an ALTER TABLE statement.
Today, you learned about all the various types. You learned about numeric types and their ranges, and
you also learned about string types. You discovered that there were two groups of string types—