Column Modifiers : Column Modifiers « Table « MySQL Tutorial





Modifier NameApplicable Types
AUTO_INCREMENTAll INT Types
BINARYCHAR, VARCHAR
DEFAULTAll, except BLOB, TEXT
NOT NULLAll Types
NULLAll Types
PRIMARY KEYAll Types
UNIQUEAll Types
UNSIGNEDNumeric Types
ZEROFILLNumeric Types


The BINARY modifier causes the values to treated as binary strings, making them case sensitive.

The DEFAULT modifier specifies the default value.

The MySQL default value is NULL for all types except ENUM.

For 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:

mysql>
mysql> CREATE TABLE Test(State char(2) NOT NULL DEFAULT "KY");
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into Test (state) value (default);
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> select * from Test;
+-------+
| State |
+-------+
| KY    |
+-------+
1 row in set (0.00 sec)

mysql>
mysql> drop table Test;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>

The NULL and NOT NULL modifiers specify nullable column.

The PRIMARY KEY is actually an index that must contain unique values.

The UNIQUE modifier enforces that all data within the declared column must be unique.











4.11.Column Modifiers
4.11.1.Column Modifiers