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
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,
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.
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.
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
CREATE TABLE courses (
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL
courses table has two columns:
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.
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
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 (
PRIMARY KEY (employee_id , course_id)
trainings table consists of three columns:
employee_idcolumn store the id of employees who took the course.
course_idcolumn store the course that employee took.
taken_datecolumn stores the date when the employee took the course.
Because the primary key of the
trainings table consists of two columns:
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.