SQL CREATE TABLE

Summary: in this tutorial, you will learn how to use the SQL CREATE TABLE statement to create new tables.

Introduction to SQL CREATE TABLE statement

So far, you have learned various ways to query data from one or more table in the sample database. It is time to learn how to create your own tables.

A table is a collection of data stored in a database. A table consists of columns and rows. To create a new table, you use the CREATE TABLE statement with the following syntax:

CREATE TABLE table_name(
     column_name_1 data_type default value column_constraint,
     column_name_2 data_type default value column_constraint,
     ...,
     table_constraint
);Code language: SQL (Structured Query Language) (sql)

The minimum required information for creating a new table is a table name and a column name.

The name of the table, given by the table_name, must be unique within the database. If you create a table whose name is the same as the one that already exists, the database system will issue an error.

In the CREATE TABLE statement, you specify a comma-separated list of column definitions. Each column definition is composed of a column name, column’s data type, a default value, and one or more column constraints.

The data type of a column specifies the type of data that column can store. The data type of the column can be the numeric, characters, date, etc.

The column constraint controls what kind of value that can be stored in the column. For example, the NOT NULL constraint ensures that the column does not contain any NULL value.

A column may have multiple column constraints. For example, the username column of the  users table can have both NOT NULL and UNIQUE constraints.

In case a constraint contains multiple columns, you use the table constraint. For example, if a table has the primary key that consists of two columns, in this case, you have to use the PRIMARY KEY table constraint.

SQL CREATE TABLE examples

Suppose you have to store the training data of employees in the database with a requirement that each employee may take zero or many training courses, and each training course may be taken by zero or many employees.

You looked at the current database and found no place to store this information, therefore, you decided to create new tables.

The following statement creates the courses table:

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Try It

The courses table has two columns: course_id and course_name.

The course_id is the primary key column of the courses table. Each table has one and only one primary key that uniquely identifies each row in the table. It is a good practice to define a primary key for every table.

The data type of the course_id is integer denoted by the INT keyword. In addition, the value of the course_id column is AUTO_INCREMENT. It means that when you insert a new row into the courses table without providing the value for the course_id column, the database system will generate an integer value for the column.

The course_name stores the names of courses. Its data type is the character string ( VARCHAR) with maximum length is 50. The NOT NULL constraint ensures that there is no NULL values stored in the course_name column.

Now you have the table to store the course data. To store the training data, you create a new table named training as follows.

CREATE TABLE trainings (
    employee_id INT,
    course_id INT,
    taken_date DATE,
    PRIMARY KEY (employee_id , course_id)
);Code language: SQL (Structured Query Language) (sql)

Try It

The trainings table consists of three columns:

• The employee_id column store the id of employees who took the course.
• The course_id column store the course that employee took.
• The taken_date column stores the date when the employee took the course.

Because the primary key of the trainings table consists of two columns: employee_id and course_id, we had to use the PRIMARY KEY table constraint.

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