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 tables 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 a 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
);

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 the 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 the column can store. The data type of the column can be 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 the 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 a 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
);

The courses the 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 a 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 as follows.

CREATE TABLE trainings (
    employee_id INT,
    course_id INT,
    taken_date DATE,
    PRIMARY KEY (employee_id , course_id)
);

The trainings the 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 the employee took.
• The taken_date column stores the date when the employee took the course.

Because the primary key of the trainings a 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 a new a new table in the database.