CREATE TABLE - Column flags

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
(column type[(width)] [ASC|DESC] [NOT NULL|NULL] [DEFAULT value]
   [AUTO_INCREMENT] [[PRIMARY] KEY]|[[UNIQUE] KEY] 
   [COMMENT 'string']
   [REFERENCES table [(column,...)]
      [MATCH FULL|MATCH PARTIAL|MATCH SIMPLE]
      [ON DELETE [RESTRICT|CASCADE|SET NULL|NO ACTION]]
      [ON UPDATE [RESTRICT|CASCADE|SET NULL|NO ACTION]] [,...]
   ]
[,...]) [options]

Explanation

This is the syntax for the CREATE TABLE statement again, but detailing the column flags portion of the column definition. For some column types, you may need to specify the size of the column within parentheses after the column name and column type.

If a column is indexed, the keyword ASC or DESC may be given next to indicate whether indexes should be stored in ascending or descending order, respectively. By default, they are stored in ascending order. For older versions of MySQL, these flags are ignored. Adding the NOT NULL flag indicates the column may not be NULL. The NULL flag may be given to state that a NULL value is allowed. Some data types by default are NULL. For some data types, you don't have a choice of whether a column may be NULL or not. To set a default value for a column, you can use the DEFAULT keyword. For some data types (e.g., TIMESTAMP), a default value is not allowed. The AUTO_INCREMENT option tells MySQL to assign a unique identification number automatically to a column. It must be designated as a PRIMARY or UNIQUE key column and you cannot have more than one AUTO_INCREMENT column in a table. If a column is to be the basis of an index, either PRIMARY KEY, UNIQUE KEY, UNIQUE, or just KEY can be given. Just KEY indicates the column is a primary key.

To document what you're doing for an administrator or a developer, a comment regarding a column may be given. The results of a SELECT statement won't show it, but a SHOW FULL COLUMNS statement will reveal it. To add a comment, use the COMMENT keyword followed by a string within quotes.

Examples

Here is an example using some of the flags and clauses mentioned here:

CREATE TABLE clients
(client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
client_name VARCHAR(75),
client_city VARCHAR(50) DEFAULT 'New Orleans',
telephone CHAR(15) COMMENT 'Format: ###-###-####');

In this example, the client_id column is a primary key. The NOT NULL option is included for completeness, even though it's not necessary, because a primary key must be unique and non-NULL. For the client_city column, the DEFAULT clause is used to provide the default value of the column. The default will be used during inserts when no value is given, although you can override the default by specifying an explicit blank value for the column. This statement also includes a comment regarding the typical format for entering telephone numbers in the telephone column. Again, this will be displayed only with the SHOW FULL COLUMNS statement.

For information on the REFERENCES column flag, see the CREATE TABLE - References page.