| 4. 11. 1. Column Modifiers |
|
|
| Modifier Name | Applicable Types | | AUTO_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 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. |