SQL CREATE TABLE

What is the SQL CREATE TABLE statement?

Defines a new table and its columns, data types, and constraints in the database.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CREATE TABLE: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, IBM Db2, Snowflake, BigQuery, Redshift, and all ANSI-compliant databases.

SQL CREATE TABLE Full Explanation

CREATE TABLE is a Data Definition Language (DDL) command that registers a new, permanently stored table in the current database schema. It specifies the table name, one or more column definitions (each with a data type and optional column-level constraints), and optional table-level constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK. The statement fails if a table with the same name already exists unless the IF NOT EXISTS clause (or dialect-specific equivalent) is supplied. Some systems allow additional options like storage engine, tablespace, partitioning rules, and comment strings. Because CREATE TABLE changes the database catalog, it is auto-committed in most systems and cannot be rolled back in databases that implicitly commit DDL. Temporary or global temporary tables can be created with dialect-specific keywords (e.g., TEMP, TEMPORARY, GLOBAL TEMPORARY).

SQL CREATE TABLE Syntax

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [column_constraints],
    ...,
    [table_constraints]
) [table_options];

SQL CREATE TABLE Parameters

table_name (identifier) - Name of the table to be created
column_name (identifier) - Name of each column
data_type (type) - Data type for the column (e.g., INT, VARCHAR, DATE)
column_constraints (keyword list) - NOT NULL, UNIQUE, PRIMARY KEY, DEFAULT, CHECK, REFERENCES
table_constraints (keyword list) - PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK applied to multiple columns
IF NOT EXISTS (clause) - Skips creation if the table already exists
TABLE_OPTIONS (dialect specific) - Storage engine, tablespace, partitioning, comments, etc.

Example Queries Using SQL CREATE TABLE

-- Basic table
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- With composite primary key and foreign key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- If the table might already exist
CREATE TABLE IF NOT EXISTS logs (
    log_id SERIAL PRIMARY KEY,
    message TEXT
);

-- Temporary table for session use
CREATE TEMPORARY TABLE temp_numbers (n INT);

-- Create from query (dialect specific)
CREATE TABLE sales_ytd AS
SELECT * FROM sales WHERE sale_date >= DATE '2024-01-01';

Expected Output Using SQL CREATE TABLE

The database catalog now contains the specified table
Subsequent SELECT, INSERT, UPDATE, or DELETE statements can target it
If the table already exists without IF NOT EXISTS, the database raises an error

Use Cases with SQL CREATE TABLE

Defining new entities in an application schema
Spinning up scratch tables for ETL staging or analytics
Creating temporary tables for complex reporting workflows
Prototyping a data model during development

Common Mistakes with SQL CREATE TABLE

Omitting a PRIMARY KEY for tables that need unique row identification
Using the wrong data type size or precision, causing truncation or overflow
Forgetting IF NOT EXISTS when deployment scripts may run multiple times
Misplacing commas between column definitions or after the last column
Assuming DDL can be rolled back when the database auto-commits it

First Introduced In

SQL-86 (ANSI X3.135-1986)

Frequently Asked Questions

What happens if I run CREATE TABLE for a table that already exists?

Most databases raise an error. Add IF NOT EXISTS to skip creation safely.

Can I roll back a CREATE TABLE statement?

Only databases with transactional DDL (like PostgreSQL) allow it. Others auto-commit as soon as the statement runs.

How do I add a foreign key while creating a table?

Include a FOREIGN KEY clause that points to the parent table and column inside the CREATE TABLE definition.

What is the difference between CREATE TABLE and CREATE TABLE AS SELECT?

CREATE TABLE builds an empty structure. CREATE TABLE AS SELECT both creates the table and fills it with data from a query.