MySQueaL Resources

CREATE TABLE Statement

This is one of the primary MySQL statements that a MySQL developer will use when first setting up a database. It's used to create MySQL tables, to which data will later be added.

Use this statement to create a new table within a database. This statement has many clauses and options. However, when creating a basic table, you can omit most of them. The TEMPORARY keyword is used to create a temporary table that can be accessed only by the current connection thread and is not accessible by other users. The IF NOT EXISTS flag is used to suppress error messages caused by attempting to create a table by the same name as an existing one. After the table name is given, either the table definition is given (i.e., a list of columns and their data types) along with table options or properties, or a table can be created based on another table. See these related pages, which describe how to:

• Set column properties regarding NULL and default values (see the related Column flags page);
• Create an index for a table based on one or more columns (see the related Index and key definitions page);
• Reference a foreign key constraint (see the related Foreign Key References);
• Specify various table options (see the Table options);
• Create a table exactly like another table (see the related page, Based on an existing table); and
• Create a table with filesystem partitions (see the related three documentation pages: Partitioning, Partition definitions, and Sub-Partition definitions pages);

Here is a simple example of how you can use the CREATE TABLE statement:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT PRIMARY KEY,
client_name VARCHAR(75), 
telephone CHAR(15));

This creates a table with three columns. The first column is called client_id and may contain integers. It will be incremented automatically as records are created. It will also be the primary key field for records, which means that no duplicates are allowed and that the rows will be indexed based on this column. The second column, client_name is a variable-width, character-type column with a maximum width of 75 characters. The third column is called telephone and is a fixed-width, character-type column with a minimum and maximum width of 15 characters. To see the results of this statement, you can use the DESCRIBE statement. There are many column data types.