CREATE TABLE syntax.

CREATE TABLE table_name ( create_definition,... )

create_definition:
  column_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
                   [ PRIMARY KEY ] [reference_definition]
  or	PRIMARY KEY ( index_column_name,... )
  or	KEY [index_name] KEY( index_column_name,...)
  or	INDEX [index_name] ( index_column_name,...)
  or	UNIQUE [index_name] ( index_column_name,...)
  or    FOREIGN KEY index_name ( index_column_name,...) [reference_definition]
  or	CHECK (expr)

type:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  or    SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  or    MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  or    INT[(length)] [UNSIGNED] [ZEROFILL]
  or    INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  or    BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  or    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or	FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or	DECIMAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or	NUMERIC[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    CHAR(length) [BINARY],
  or    VARCHAR(length) [BINARY],
  or    DATE
  or    TIME
  or    TIMESTAMP
  or    DATETIME
  or    TINYBLOB
  or    BLOB
  or    MEDIUMBLOB
  or    LONGBLOB
  or    TINYTEXT
  or    TEXT
  or    MEDIUMTEXT
  or    ENUM(value1,value2,value3...)
  or    SET(value1,value2,value3...)

index_column_name:
        column_name [ (length) ]

reference_definition:
        REFERENCES table_name [( index_column_name,...)]
                   [ MATCH FULL | MATCH PARTIAL]
                   [ ON DELETE reference_option]
                   [ ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

See section Column types.

The FOREIGN KEY, CHECK and REFERENCE syntax are only for compatibility. (To make it easier to port code from other SQL servers and run applications that create tables with references). They don't actually do anything. See section What functionality is missing in MySQL.

If a column doesn't have a DEFAULT value and is not declared as NOT NULL, the default value is NULL.

If a column doesn't have a DEFAULT value and is declared as NOT NULL, MySQL will automaticly assign a default value for the field.

• ZEROFILL means that number is pre-zeroed to maximal length. With INT(5) ZEROFILL a value of 5 is retrieved as 00005.
• BINARY means that the column will be compared case sensitive. The default is that all strings are compared case insensitive according to ISO-8859-1 Latin1. BINARY is 'sticky' which means that if a column marked BINARY is used in a expression, the whole expression is compared BINARY.
• KEY is a synonym for INDEX.
• UNIQUE is in MySQL a key that can only have distinct values. You will get an error if you try add a new row with a key that matches an old row.
• PRIMARY KEY is a unique KEY. One can only have one PRIMARY KEY in a table.
• If one doesn't assign a name to an index, the index will get the same name as the first key_column with an optional _# to make it unique.
• Index columns and timestamp columns can't be NULL. For these columns the NULL attribute is silently removed.
• With column_name(length) syntax one can specify an index which is only a part of a string column. This can make the index file much smaller.
• A number column may have the additional attribute AUTO_INCREMENT to automatically get the largest value+1 for each insert where column value is NULL or 0. See section How can I get the unique ID for the last inserted row?
• One can insert NULL for timestamp and auto_increment columns. This results in the current time / the next number.
• Blob columns can't be index.
• When one groups on a blob only the first 'max_sort_length' bytes are used. See section Limitations of BLOB and TEXT types.
• Deleted records are in a linked list and subsequent inserts will reuse old positions. To get smaller files one can use the isamchk utility to reorganise tables.
• Each null column takes one bit extra, rounded up to the nearest byte.
• The maximum record length can be calculated as follows: 1+ sum_of_column_lengths + null_columns/8 + number of variable length columns.
• In some cases an attribute may silently change after creation: VARCHAR columns with a length of one or two are changed to CHAR. When using one VARCHAR column all CHAR columns longer than 2 are changed to VARCHAR's. This doesn't affect the usage of the column in any way; In MySQL VARCHAR is just a different way to store characters. MySQL does the conversion because it will save space and make the table faster. See section What are the different row formats? Or when to use VARCHAR/CHAR?
• On INSERT/UPDATE all strings (CHAR and VARCHAR) are silently chopped/padded to the maximal length given by CREATE. All end spaces are also automatically removed. For example VARCHAR(10) means that the column can contain strings with a length up to 10 characters.
• Something/0 gives a NULL value.
• The regular expression function (REGEXP and RLIKE) uses ISO8859-1 (Latin1) when deciding the type of a character.

Go to the first, previous, next, last section, table of contents.