MySQL Reference Manual for version 3.23.5-alpha.

7.6 `CREATE TABLE` syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] (index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [INDEX] [index_name] (index_col_name,...)
  or    [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_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    LONGTEXT
  or    ENUM(value1,value2,value3,...)
  or    SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

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

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

table_options:
	TYPE = {ISAM | MYISAM | HEAP}
or	AUTO_INCREMENT = #
or	AVG_ROW_LENGTH = #
or	CHECKSUM = {0 | 1}
or	COMMENT = "string"
or	MAX_ROWS = #
or	MIN_ROWS = #
or	PACK_KEYS = {0 | 1}
or	PASSWORD = "string"
or	DELAY_KEY_WRITE = {0 | 1}

select_statement:
	[IGNORE | REPLACE] SELECT ...  (Some legal select statement)

CREATE TABLE creates a table with the given name in the current database. Rules for allowable table names are given in 7.1.5 Database, table, index, column and alias names. An error occurs if there is no current database or if the table already exists.

In MySQL 3.22 or later, the table name can be specified as db_name.tbl_name. This works whether or not there is a current database.

In MySQL 3.23, you can use the TEMPORARY keyword when you create a table. A temporary table will automatically be deleted if a connection dies and the name is per connection. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted).

In MySQL 3.23 or later, you can use the keywords IF NOT EXISTS so that an error does not occur if the table already exists. Note that there is no verification that the table structures are identical.

Each table tbl_name is represented by some files in the database directory. In the case of ISAM-type tables you will get:

File	Purpose
`tbl_name.frm`	Table definition (form) file
`tbl_name.ISD`	Data file
`tbl_name.ISM`	Index file

For more information on the properties of the various column types, see 7.2 Column types.

If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.
An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. mysql_insert_id(). If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused. If you delete all rows in the table, the sequence starts over. Note: There can be only one AUTO_INCREMENT column per table, and it must be indexed. To make MySQL compatible with some ODBC applications, you can find the last inserted row with the following query:
```
SELECT * FROM tbl_name WHERE auto_col IS NULL
```
NULL values are handled differently for TIMESTAMP columns than for other column types. You cannot store a literal NULL in a TIMESTAMP column; setting the column to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them. On the other hand, to make it easier for MySQL clients to use TIMESTAMP columns, the server reports that such columns may be assigned NULL values (which is true), even though TIMESTAMP never actually will contain a NULL value. You can see this when you use DESCRIBE tbl_name to get a description of your table. Note that setting a TIMESTAMP column to 0 is not the same as setting it to NULL, because 0 is a valid TIMESTAMP value.
If no DEFAULT value is specified for a column, MySQL automatically assigns one. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type:
- For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT column, the default value is the next value in the sequence.
- For date and time types other than TIMESTAMP, the default is the appropriate ``zero'' value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. 7.2.6 Date and time types.
- For string types other than ENUM, the default is the empty string. For ENUM, the default is the first enumeration value.
KEY is a synonym for INDEX.
In MySQL, a UNIQUE key can have only distinct values. An error occurs if you try to add a new row with a key that matches an existing row.
A PRIMARY KEY is an unique KEY with the extra constraint that all key columns must be defined as NOT NULL. In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY.
A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attibute in a column specification. Doing so will mark only that single column as primary. You must use the PRIMARY KEY(index_col_name, ...) syntax.
If you don't assign a name to an index, the index will be assigned the same name as the first index_col_name, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. SHOW.
Only the MyISAM table type supports indexes on columns that can have NULL values. In other cases you must declare such columns NOT NULL or an error results.
With col_name(length) syntax, you can specify an index which uses only a part of a CHAR or VARCHAR column. This can make the index file much smaller. 7.2.9 Column indexes.
Only the MyISAM table type supports indexing on BLOB and TEXT columns. When putting an index on a BLOB or TEXT column you MUST always specify the length of the index:
```
CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
```
When you use ORDER BY or GROUP BY with a TEXT or BLOB column, only the first max_sort_length bytes are used. BLOB.
The FOREIGN KEY, CHECK and REFERENCES clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. 5.3 Functionality missing from MySQL.
Each NULL column takes one bit extra, rounded up to the nearest byte.

The maximum record length in bytes can be calculated as follows:

row length = 1
             + (sum of column lengths)
             + (number of NULL columns + 7)/8
             + (number of variable-length columns)

The table_options and SELECT options is only implemented in MySQL 3.23 and above. The different table types are:

ISAM	The original table handler
MyISAM	The new binary portable table handler
HEAP	The data for this table is only stored in memory

10.18 MySQL table types.. The other table options are used to optimize the behavior of the table. In most cases, you don't have to specify any of them. The options work for all table types, if not otherwise indicated.

`AUTO_INCREMENT`	The next auto_increment value you want to set for your table (MyISAM)
`AVG_ROW_LENGTH`	An approximation of the average row length for your table. You only need to set this for tables with variable size records.
`CHECKSUM`	Set this to 1 if you want MySQL to maintain a checksum for all rows (makes the table a little slower to update but makes it easier to find corrupted tables) (MyISAM)
`COMMENT`	A 60 character comment for your table
`MAX_ROWS`	Max number of rows you plan to store in the table
`MIN_ROWS`	Minimum number of rows you plan to store in the table
`PACK_KEYS`	Set this to 1 if you want to have smaller index. This usually makes updates slower and reads faster (MyISAM, ISAM).
`PASSWORD`	Encrypt the `.frm` file with a password. This option doesn't do anything in the standard MySQL version.
`DELAY_KEY_WRITE`	Set this to 1 if want to delay key table updates until the table is closed (MyISAM).

When you use a MyISAM table, MySQL uses the product of max_rows * avg_row_length to decide how big the resulting table will be. If you don't specify any of the above options, the maximum size for a table will be 4G (or 2G if your operating systems only supports 2G tables).

If you specify a SELECT after the CREATE STATEMENT, MySQL will create new fields for all elements in the SELECT. For example:
```
mysql> CREATE TABLE test (a int not null auto_increment,
           primary key (a), key(b))
           TYPE=HEAP SELECT b,c from test2;
```
This will create a HEAP table with 3 columns. Note that the table will automatically be deleted if any errors occur while copying data into the table.

7.6.1 Silent column specification changes

In some cases, MySQL silently changes a column specification from that given in a CREATE TABLE statement. (This may also occur with ALTER TABLE.)

VARCHAR columns with a length less than four are changed to CHAR.
If any column in a table has a variable length, the entire row is variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columnss. This doesn't affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster. 10.17 What are the different row formats? Or, when should VARCHAR/CHAR be used?.
TIMESTAMP display sizes must be even and in the range from 2 to 14. If you specify a display size of 0 or greater than 14, the size is coerced to 14. Odd-valued sizes in the range from 1 to 13 are coerced to the next higher even number.
You cannot store a literal NULL in a TIMESTAMP column; setting it to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them. DESCRIBE tbl_name always reports that a TIMESTAMP column may be assigned NULL values.
MySQL maps certain column types used by other SQL database vendors to MySQL types. 7.2.11 Using column types from other database engines.

If you want to see whether or not MySQL used a column type other than the one you specified, issue a DESCRIBE tbl_name statement after creating or altering your table.

Certain other column type changes may occur if you compress a table using pack_isam. 10.17 What are the different row formats? Or, when should VARCHAR/CHAR be used?.

7.6 CREATE TABLE syntax

7.6.1 Silent column specification changes

7.6 `CREATE TABLE` syntax