CREATE TABLE - Index and key definitions

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, ..., index type[(width)] [ASC|DESC] | 
[CONSTRAINT [symbol]] PRIMARY KEY [type] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] | 
INDEX|[PRIMARY] KEY [index] [type] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] |
[CONSTRAINT [symbol]] UNIQUE [INDEX] [index] [type] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] |
[FULLTEXT|SPATIAL] [INDEX] [index] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] |
[CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...)
    [reference_definition] | 
CHECK (expression)]
[,...]) [options]

Explanation

You can use one or more columns for an index, and a table can contain multiple indexes. Indexes can greatly increase the speed of data retrieval from a table. You can define an index involving multiple columns with this statement, or later with the ALTER TABLE statement or the CREATE INDEX statement. With the CREATE TABLE statement, though, indexes can be given after the definition of the columns they index.

A KEY (also called a PRIMARY KEY) is a particular kind of index obeying certain constraints: it must be unique, for instance. It is often combined in MySQL with the AUTO_INCREMENT keyword, and used for identifiers that appear as columns in tables. The general format is to specify the type of index, such as KEY, INDEX, or UNIQUE. This is followed by the index name. Optionally, the index type may be specified with the USING keyword. For most tables there is only one type of index, so this is unnecessary.

Before version 5 of MySQL, BTREE is the only type for MyISAM tables. Beginning with version 5, the RTREE index type is also available, so you may want to specify the index type. After the index type, one or more columns for which the index is based are listed within parentheses, separated by commas.

Examples

Before explaining the various possibilities, let's look at an example:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
name_last VARCHAR(50), name_first VARCHAR(50),
telephone CHAR(15),
INDEX names USING BTREE (name_last(5), name_first(5) DESC));

In this example, the client_id column is a PRIMARY KEY, although that clause has been abbreviated to just KEY. This abbreviation is available as of version 4.1 of MySQL. There can be only one PRIMARY KEY, but any number of other indexes. The table contains a second index using the first five characters of the two name columns. To specify a combination, the index definition is generally given at the end of the table's column definitions with the INDEX keyword. The index is named names in the example.

After the index name, the USING clause specifies the type of index to be used. Currently, this is unnecessary because BTREE is the default type for a MyISAM table. In fact, before version 5 of MySQL, BTREE was the only type of index available for MyISAM tables. Beginning with version 5, the RTREE index type is also available, so you may want to specify the index type.

Next, the two columns to index appear within parentheses. The name columns are variable-width columns and 50 characters in length, so to speed up indexing only the first five characters of each column are used. The name_first column is supposed to be used in descending order per the DESC flag. However, this will be ignored for the current version of MySQL.

The syntax structures for the index clauses listed here vary depending on the type of table index to be created: PRIMARY KEY, INDEX, UNIQUE, FULLTEXT (or BLOB column types), or SPATIAL.

To create constraints on tables based on columns in another table, use the FOREIGN KEY index syntax structures. Foreign keys are used only to link columns in InnoDB tables. The CHECK clause is not used in MySQL but is available for porting to other database systems. Here is an example of how you can use foreign keys to create a table:

CREATE TABLE employees
(rec_id INT NOT NULL PRIMARY KEY,
name_last VARCHAR(25), name_first VARCHAR(25))
TYPE = INNODB;
CREATE TABLE programmers
(rec_id INT, emp_id INT,
INDEX (emp_id),
FOREIGN KEY (emp_id) REFERENCES employees(rec_id)
ON DELETE CASCADE)
TYPE=INNODB;

The first CREATE statement creates a table of basic employee information. The second CREATE statement creates a simple table of programmers. In the employees table, the key column rec_id will be used to identify employees and will be the foreign key for the programmers table. The programmers table sets up an index based on emp_id, which will be tied to the rec_id column in the employees table. The FOREIGN KEY clause establishes this connection using the REFERENCES keyword to indicate the employees table and the key column to use in that table. Additionally, the ON DELETE CASCADE clause instructs MySQL to delete the row in the programmers table whenever an employee record for a programmer is deleted from the employees table.

The next subsection gives the syntax for references to foreign keys and the meaning of each component.

At the end of both of these SQL statements, the storage engine is set to InnoDB with the TYPE clause. The ENGINE keyword could have been used instead and would have the same effect.

To give the storage engine a hint of the size of index key blocks, use the KEY_BLOCK_SIZE option. Set the value to 0 to instruct the engine to use the default. This option was added in version 5.1.10 of MySQL.

The WITH PARSER clause may be used to give a parser plugin for an index. This is used only with FULLTEXT indexes.