SQL CREATE TABLE Statement

In this tutorial, we'll learn about creating tables in SQL with examples.

A database table is used to store records (data). To create a database table, we use the SQL CREATE TABLE statement. For example,

CREATE TABLE Companies (
  id int,
  name varchar(50),
  address text,
  email varchar(50),
  phone varchar(10)
);

Here, the SQL command creates a database named companies. The table contains column (field) id, name, address, email and phone.

The int, varchar(50) and text are data types that tell what data could be stored in that field. Some commonly used data types are as follows.

Data Type Description Example
int can store numbers 400, -300
varchar(x) can store variable characters with maximum length of x John Doe, United States of America
text can store texts up to 65535 characters This is a really long paragraph that can go over lines.

Note: We must provide data types for each column while creating a table. Learn more about SQL Data Types.


CREATE TABLE IF NOT EXISTS

While creating a table that already exists, throws an error. To fix this issue, we can add the optional IF NOT EXISTS command while creating a table. For example,

CREATE TABLE IF NOT EXISTS Companies (
  id int,
  name varchar(50),
  address text,
  email varchar(50),
  phone varchar(10)
);

Here, the SQL command will only create a table if there is not one with a similar name.


CREATE TABLE AS

We can also create a table using records from any other existing table using the CREATE TABLE AS command. For example,

CREATE TABLE USACustomers
AS (
  SELECT *
  FROM Customers
  WHERE country = 'USA'
);

Here, the SQL command creates a table named USACustomers and copies the records of the nested query into the new table.


Related Topics: CREATE TABLE

How to create a table with a Primary Key?

To create a primary key, we can write the following command.

In MySQL

CREATE TABLE Companies (
  id int,
  name varchar(50),
  address text,
  email varchar(50),
  phone varchar(10),
  PRIMARY KEY (id)
);

In Oracle and SQL Server

CREATE TABLE Companies (
  id int NOT NULL PRIMARY KEY,
  name varchar(50),
  address text,
  email varchar(50),
  phone varchar(10)
);

To learn more, visit SQL PRIMARY KEY.

How to define constraints while creating a table?

We can also add different types of constraints while creating a table. For example,

CREATE TABLE Companies (
  id int NOT NULL,
  name varchar(50) NOT NULL,
  address text,
  email varchar(50) NOT NULL,
  phone varchar(10)
);

Here, the constraint NOT NULL is added to the columns id, name and email. It simply means, these columns can't be empty (NULL).

Note: Sometimes these constraints are database specific, meaning that these keywords may vary database to database. We'll cover the major databases in our tutorials.


Recommended Readings

Did you find this article helpful?