Summary: in this tutorial, you will learn how to use the SQL CREATE TABLE
statement to create a new table in the database.
Introduction to SQL CREATE TABLE statement #
In relational databases, a table is a structured set of data organized into rows and columns:
- Rows represent records.
- Columns represent attributes of data.
To create a new table, you use the CREATE TABLE
statement. Here’s the basic syntax of the CREATE TABLE
statement
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
Code language: SQL (Structured Query Language) (sql)
In this syntax:
table_name
is the name of the table you want to create.column1
,column2
, … are the column names of the table.- Each table column is associated with a data type it can store and an optional rule for data. In the database, we call this rule a constraint.
SQL CREATE TABLE statement example #
The following example uses the CREATE TABLE
statement to create a new table called courses
that stores the course name, description, and duration:
CREATE TABLE courses (
name VARCHAR(255) NOT NULL,
description TEXT,
duration DEC(4, 2) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
The courses
table has three columns:
name
column stores the course name. The data type of thename
column isVARCHAR
with a maximum of255
characters. If you store a string that larger than this, the database system will issue an error. TheNOT NULL
is a constraint to ensure that thename
column will always have data. In other words, it must not haveNULL
.description
column stores the course’s description. The data type of thedescription
column isTEXT
which can store very large text. Thedescription
has no constraints likeNOT NULL
. It means that you can storeNULL
in the column.duration
column stores the course’s duration in hours such as4.5
. The data type of theduration
column isDEC(4,2)
that stores exact decimal numbers.
IF NOT EXISTS option #
The database system will issue an error if you attempt to create a table that already exists. To avoid the error, you can use the IF EXISTS
option in the CREATE TABLE
statement:
CREATE TABLE IF NOT EXIST table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
Code language: SQL (Structured Query Language) (sql)
The CREATE TABLE
statement with the IF NOT EXISTS
option creates a table only when the table does not exist.
If the table already exists, the database system may issue a warning or notice and won’t do anything else.
Summary #
- A database is a collection of tables.
- A table stores a list of rows or records.
- Use the
CREATE TABLE
statement to create a new table. - Use the
CREATE TABLE
statement with theIF NOT EXISTS
option to create a table only if it does not exist.