intro
Let's explore everything you need to know about the SQL CREATE TABLE statement, a pillar of DDL in SQL!
As database users, most of us have likely created a table at least once. But do you truly understand all the details of the SQL CREATE TABLE statement? Read this guide and find out!
If you are a beginner or someone looking to learn how databases work, this article will provide the foundational knowledge you need to get started.
Let’s dive in!
What Is the SQL CREATE TABLE Statement?
The SQL CREATE TABLE statement is used to add a new table to a database. It is one of the primary SQL Data Definition Language (DDL) commands.
The CREATE TABLE statement allows you to define a new table by specifying its name, columns, their data types, and optional features such as indexes and constraints—such as primary keys and foreign keys.
How CREATE TABLE Works in SQL
At a low level, when you launch a CREATE TABLE statement, the database management system usually performs the following steps:
Once these steps are completed, there are two possible outcomes:
CREATE TABLE SQL Query Syntax
At a high level, this is what a CREATE TABLE SQL query looks like:
1
CREATE TABLE table_name (
2
column_1 data_type_1 [constraints_1],
3
column_2 data_type_2 [constraints_2],
4
...
5
column_n data_type_n [constraints_n]
6
);
This creates a table called table_name with a number n of columns, each with its own SQL data type and optional constraints.
So, in short:
Keep in mind that each database offers unique features, such as creating temporary tables, partitions, and more. To explore these specific options, refer to the official documentation pages:
SQL CREATE TABLE: Examples
Now that you know the syntax of the SQL CREATE TABLE statement, learn how to create a table—whether using a query or a database client.
In both cases, we will assume the goal is to create a table for storing product data from an e-commerce platform.
Explore how to create tables in SQL!
With a Query
The easiest way to create a table is to run a CREATE TABLE statement directly, as shown below:
1
CREATE TABLE products (
2
id INT AUTO_INCREMENT PRIMARY KEY,
3
name VARCHAR(255) NOT NULL,
4
price DECIMAL(10, 2),
5
stock INT,
6
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
7
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
8
);
This MySQL query creates a products table with columns for the ID of the product, its name, price, stock quantity, category ID, a timestamp for when the product was added, and another for when it was last updated.
The name column cannot be null, as specified by the NOT NULL constraint, while price and stock can store null values. Additionally, the created_at and updated_at columns default to the current timestamp, ensuring automatic time tracking for new and updated records.
With a Visual Database Client
Creating a table with a CREATE TABLE SQL query is straightforward, but remembering the syntax and unique features across different databases might be challenging. To simplify this process, consider using a visual database client like DbVisualizer.
DbVisualizer enables you to create a table with a simplified procedure through an intuitive UI. To get started:
Navigate to the "Tables" dropdown of your database (
ecommerce, in this case) on the left, right-click on it, and select the "Create Table…" option:
Selecting the “Create Table…” option in DbVisualizer
This will open the following modal:
Now, give your table a name, then click the "+" button to add columns, specify column names, data types, and constraints, as shown in the GIF:
Once done, click the "Execute" button to run the query, which you can preview in the lower panel. After execution, the products table will appear in the "Tables" dropdown. Select it, go to the "DDL" tab, and confirm it matches the specified column structure:
The SQL DDL definition of the “products” table created visually
Great! With DbVisualizer, table creation becomes significantly more accessible and efficient.
Best Practices of Using CREATE TABLE in SQL
Follow these CREATE TABLE SQL best practices for efficient and error-free table creation:
Conclusion
In this guide, you explored the SQL CREATE TABLE command and learned how to use it across the most popular databases. Here, you saw how visual tools like DbVisualizer can simplify the process of creating tables.
This powerful database client streamlines tasks such as query execution, data exploration, and table management. It also provides advanced features like query optimization, SQL formatting, and schema visualization with ERD-like diagrams. Try DbVisualizer for free today!
FAQ
Is the SQL CREATE TABLE statement part of the SQL standard?
Yes, the SQL CREATE TABLE statement is part of the SQL/ANSI standard. This is why so many databases, including the most popular ones support it.
What are the particularities of the SQL Server CREATE TABLE statement?
The SQL Server CREATE TABLE statement particularities are:
Additionally, SQL Server provides features for table partitioning and more paculiar aspects.
How to create a temp table in SQL?
To create a temporary table in MySQL and PostgreSQL, use the CREATE TEMPORARY TABLE statement. For example:
1
CREATE TEMPORARY TABLE temp_table (
2
id INT,
3
name TEXT
4
);
In SQL Server, temporary tables are created using the # prefix:
1
CREATE TABLE #temp_table (
2
id INT,
3
name TEXT
4
);
Temporary tables exist only during the session or until the connection is closed. They are ideal for storing intermediate results in complex queries without affecting permanent database structures.
For more details, refer to our guide on creating temporary tables with SQL.
How to create a table based on another table in SQL?
To create a table based on another table in SQL, use the CREATE TABLE ... AS statement. This creates a new table with the structure and optionally the data of the original table. For example:
1
CREATE TABLE new_table AS SELECT * FROM original_table;
new_table will have the same structure and data of original_table. For more information, refer to our guide on how to create a table based on another table in MySQL.
What happens behind the scenes when you create a table in MySQL?
When you create an InnoDB table in MySQL using a file-per-table tablespace or general tablespace, the table data and associated indexes are stored in a .ibd file within the database directory. If the table is created in the system tablespace, the data and indexes are stored in the ibdata* files, which represent the system tablespace. For more details on how different storage engines manage table files, refer to the Alternative Storage Engines page of the official docs.
