Creating Tables Using MySQL CREATE TABLE Statement?

Summary: in this tutorial, we will show you how to create new tables in a database using MySQL CREATE TABLE statement.

MySQL CREATE TABLE syntax

In order to create a new table within a database, you use the MySQL CREATE TABLE  statement. The CREATE TABLE  statement is one of the most complex statement in MySQL.

The following illustrates the syntax of the CREATE TABLE  statement in the simple form:

1 2 3

CREATE TABLE [IF NOT EXISTS] table_name(         column_list         ) engine=table_type

Let’s examine the syntax in greater detail:

• First, you specify the name of the table that you want to create after the CREATE TABLE  clause. The table name must be unique within a database. The IF NOT EXISTS   is an optional part of the statement that allows you to check if the table that you are creating already exists in the database. If this is the case, MySQL will ignore the whole statement and will not create any new table. It is highly recommended that you use IF NOT EXISTS  in every CREATE TABLE  statement for preventing from an error of creating a new table that already exists.
• Second, you specify a list of columns for the table in the column_list section. Columns are separated by a comma (,).  We will show you how to define columns in more detail in the next section.
• Third, you need to specify the storage engine for the table in the engine clause. You can use any storage engine such as InnoDB, MyISAM, HEAP, EXAMPLE, CSV, ARCHIVE, MERGE FEDERATED or NDBCLUSTER. If you don’t declare the storage engine explicitly, MySQL will use InnoDB by default.

InnoDB became the default storage engine since MySQL version 5.5. The InnoDB table type brings many benefits of relational database management system such as ACID transaction, referential integrity, and crash recovery. In previous versions, MySQL used MyISAM as the default storage engine.

To define a column for the table in the CREATE TABLE  statement, you use the following syntax:

1 2	column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]

The most important components of the syntax above are:

• The column_name  specifies the name of the column. Each column has a specific data type and the size e.g.,VARCHAR(255) 
• The  NOT NULL  or NULL indicates that the column accepts NULL  value or not.
• The DEFAULT  value is used to specify the default value of the column.
• The AUTO_INCREMENT  indicates that the value of the column is increased automatically whenever a new row is inserted into the table. Each table has one and only one AUTO_INCREMENT  column.

If you want to set particular columns of the table as the primary key, you use the following syntax:

1	PRIMARY KEY (col1,col2,...)

Example of MySQL CREATE TABLE statement

Let’s practice with an example of creating a new table named tasks  in our sample database as follows:

You can use the CREATE TABLE  statement to create the tasks  table as follows:

1 2 3 4 5 6 7 8

CREATE TABLE IF NOT EXISTS tasks (   task_id INT(11) NOT NULL AUTO_INCREMENT,   subject VARCHAR(45) DEFAULT NULL,   start_date DATE DEFAULT NULL,   end_date DATE DEFAULT NULL,   description VARCHAR(200) DEFAULT NULL,   PRIMARY KEY (task_id) ) ENGINE=InnoDB

In this tutorial, you have learned how to use MySQL CREATE TABLE statement to create a new table in a database.