SQL CREATE TABLE Statement

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)

Try it

The courses table has three columns:

• name column stores the course name. The data type of the name column is VARCHAR with a maximum of 255 characters. If you store a string that larger than this, the database system will issue an error. The NOT NULL is a constraint to ensure that the name column will always have data. In other words, it must not have NULL.
• description column stores the course’s description. The data type of the description column is TEXT which can store very large text. The description has no constraints like NOT NULL. It means that you can store NULL in the column.
• duration column stores the course’s duration in hours such as 4.5. The data type of the duration column is DEC(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 the IF NOT EXISTS option to create a table only if it does not exist.

Databases #

Quiz #