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 how to query data from one or more tables. It’s time for you to learn how to create a new table by yourself. In this tutorial, we will show you how to do it.
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 as follows:
CREATE TABLE table_name(
column_name data_type 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
table_name, must be unique. 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 body of the
CREATE TABLE statement, you specify a comma-separated list of column definitions. Each column definition is composed of a column name, a data type, and a comma-separated list of column constraints.
The data type of the column specifies which kind of data that column can store. The data type can be the integer, 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 in the users table is NOT NULL and UNIQUE, hence, two constraints are associated with the column.
In case a constraint contains multiple columns, you can 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 example
Suppose, you have to store the training data of employees in the database with the 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.
To create the courses table, you use the following statement:
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL
The 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. It is a good practice that you should define a primary key for every table.
The primary key may consists of one or more columns. You use the PRIMARY KEY column constraint to define the
course_id column as the primary key of the
The data type of the
course_id is integer denoted by the INT keyword. In addition, the value of the
course_id column is
AUTOINCREMENT. It means that when you insert a new row into the
courses table without providing the value for the
course_id column, the database will generate an integer value as the next integer value.
course_name stores the course names. Its data type is the string (VARCHAR) with maximum length is 50. The NOT NULL constraint ensures that there are 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.
CREATE TABLE training (
PRIMARY KEY (employee_id , course_id)
The training table consists of three columns:
- The employee_id column specifies the employee who took the course.
- The course_id column specifies the course that employee took.
- The taken_date column stores the date when the employee took the course.
Because the primary key of the training table consists of two columns: employee_id and course_id, you have to use the PRIMARY KEY table constraint.
In this tutorial, we have shown you how to use the SQL CREATE TABLE statement to create new tables.