SQL CREATE

What does the SQL CREATE statement do?

CREATE instantiates new database objects such as tables, views, schemas, or indexes.

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: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery, DuckDB, and virtually all ANSI-compliant databases

SQL CREATE Full Explanation

The CREATE statement is the foundational Data Definition Language (DDL) command used to add new objects to a SQL database. By specifying an object type (TABLE, VIEW, DATABASE, SCHEMA, INDEX, PROCEDURE, FUNCTION, ROLE, etc.) and a unique name, CREATE permanently registers the object in the system catalog. Optional clauses like IF NOT EXISTS, OR REPLACE, and TEMPORARY control existence checks, replacement behavior, and session scope. Because CREATE changes metadata, most engines auto-commit the transaction before and after execution; rolling back is not always possible once the object is visible. Permissions are enforced: the issuing role must hold CREATE privilege at the appropriate scope (database, schema, or server). Some dialects extend CREATE with object-specific options (e.g., PostgreSQL PARTITION BY on tables or MySQL ENGINE).

SQL CREATE Syntax

-- Generic template
CREATE [OR REPLACE] <OBJECT_TYPE>
    [IF NOT EXISTS] object_name
    <object_specific_definition>;

-- Common variants
CREATE TABLE IF NOT EXISTS schema.table_name (
    column_name data_type [column_constraints],
    ...
);

CREATE VIEW view_name AS
SELECT ...;

CREATE DATABASE new_database;

CREATE INDEX idx_name ON table_name (column1, column2);

SQL CREATE Parameters

OBJECT_TYPE (keyword) - Type of object to create (TABLE, VIEW, DATABASE, INDEX, etc.)
IF NOT EXISTS (clause) - Prevents error if object already exists (supported by most modern engines)
OR REPLACE (clause) - Drops the existing object of the same name and recreates it (not in MySQL)
object_name (identifier) - Name of the object, optionally qualified by schema
object_specific_definition (varies) - Columns, constraints, query, or options depending on OBJECT_TYPE

Example Queries Using SQL CREATE

-- 1. Create a table only if it does not yet exist
CREATE TABLE IF NOT EXISTS sales.orders (
    order_id     SERIAL PRIMARY KEY,
    customer_id  INT NOT NULL,
    order_total  NUMERIC(12,2) DEFAULT 0,
    created_at   TIMESTAMP DEFAULT NOW()
);

-- 2. Replace an existing view with a new definition
CREATE OR REPLACE VIEW analytics.top_customers AS
SELECT customer_id, SUM(order_total) AS lifetime_value
FROM   sales.orders
GROUP  BY customer_id
ORDER  BY lifetime_value DESC;

-- 3. Create an index to speed up lookups
CREATE INDEX idx_orders_customer_id
    ON sales.orders (customer_id);

-- 4. Make a temporary table for the current session
CREATE TEMPORARY TABLE temp_session_data (
    id   INT,
    note TEXT
);

Expected Output Using SQL CREATE

After each statement, the specified object appears in the system catalog and becomes immediately usable
If IF NOT EXISTS is present and the object is already there, the engine returns a notice rather than an error
OR REPLACE first drops the prior object (cascading where required) then recreates it
Successful execution usually commits automatically

Use Cases with SQL CREATE

Defining the initial schema of a new application
Adding indexes or materialized views to improve performance
Creating temporary scratch tables for ETL jobs
Replacing outdated views while keeping the same object name for downstream dependencies
Provisioning databases, roles, or schemas in deployment scripts

Common Mistakes with SQL CREATE

Omitting IF NOT EXISTS, causing errors in idempotent deployment scripts
Expecting OR REPLACE to keep dependent permissions or grants (they are often dropped)
Forgetting to qualify object names with schema, leading to ambiguous or incorrectly placed objects
Assuming a CREATE inside a transaction will roll back on error (many engines auto-commit DDL)
Using reserved keywords as object names without quoting

First Introduced In

SQL-86 (ANSI X3.135-1986)

Frequently Asked Questions

What objects can be created with SQL CREATE?

CREATE can build tables, views, databases, schemas, sequences, indexes, functions, procedures, roles, and other objects supported by your database.

How can I avoid an error if the object already exists?

Include IF NOT EXISTS. The statement becomes idempotent and returns a harmless notice when the name is taken.

Is CREATE OR REPLACE safe for production views?

It is generally safe but will drop and recreate the view, which removes dependent grants in many systems. Re-grant permissions afterward.

Does a CREATE statement get rolled back if my transaction fails?

DDL usually triggers an implicit commit in engines like MySQL and Oracle. PostgreSQL is transactional for most DDL, but many other systems are not. Assume CREATE cannot be rolled back unless your dialect explicitly supports transactional DDL.