SQL CREATE TABLE Statement

The SQL CREATE TABLE statement is used to create and define a table.

A table organizes the information into rows and columns.

Syntax

The syntax of the CREATE TABLE statement is as follows:

CREATE TABLE table_name (
    column1 data_type constraints,
    column2 data_type constraints,
    ...
);

Parameters:

• table_name: The name of the table that you want to create.
• colunm1, column2: Columns that you want to create in the table. Each column must have a data type

For an overview of the available data types, please check out the reference section SQL data types.

The above syntax of CREATE TABLE also use constraints. Constraints define rules concerning the values accepted in columns. The following list shows some of the constraints that can be applied to a column:

• PRIMARY KEY constraint identifies the corresponding column as the table's primary key.
• AUTO_INCREMENT attribute is a MySQL extension to standard SQL, which tells MySQL to automatically assign a new value by incrementing the previous value by 1. It is used for the numeric column. The column will be automatically set if the field is left unspecified.
• NOT NULL constraint ensures that the column does not allow a NULL value.
• UNIQUE constraint ensures that each row for a column to be a unique value.

We will learn more about constraints in the next chapters.

Note: You can run the command DESC table_name to see the structure of any table in MySQL and Oracle.

SQL CREATE TABLE Example

The following SQL statement will create a table called "Student".

-- Syntax for MySQL database

CREATE TABLE student (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(30) NOT NULL,
    last_name  VARCHAR(30) NOT NULL,
    birth_date DATE,
    phone VARCHAR(20) NOT NULL UNIQUE
);

The above SQL statement creates a table named "Student" with five columns (id, first_name, last_name, birth_date, phone). We can see that a data type definition follows each column name. This definition indicates what type of data the column will store.

Create Table From Another Table

The CREATE TABLE statement also can be used to copy an existing table.

The newly created table will have the same column definitions. All or some columns can be selected.

If you create a new table from an existing one, the new table will have records from the old table.

Syntax

The syntax for creating a new table from an existing one using the CREATE TABLE statement is as follows:

CREATE TABLE new_table_name AS 
    SELECT column1, column2, ...columnN
    FROM old_table_name
    WHERE condition;

Example

The following SQL statement will create a new table called "New_student" which will copy the already existing "Student" table.

CREATE TABLE new_student AS 
    SELECT first_name, last_name, birth_date
    FROM student;

As you can see, the "New_student" table contains just three columns (first_name, last_name, birth_date).

Create Table If Not Exists

If you try to create a table that already exists in the database, the database system will send an error message. If you want to avoid the error message, you can use the IF NOT EXISTS as follows:

-- Syntax for MySQL database

CREATE TABLE student (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(30) NOT NULL,
    last_name  VARCHAR(30) NOT NULL,
    birth_date DATE,
    phone VARCHAR(20) NOT NULL UNIQUE
);

Note: if you want to see the list of tables inside the currently selected database, you can run the SHOW TABLES; statement for the MySQL database.