SQL guide · Tables, views & databases (DDL)

SQL CREATE TABLE: How to Create a Table with Columns and Constraints

The CREATE TABLE statement defines a new table in the current database. You give the table a name, list its columns, and assign each column a data type such as an integer, a piece of text, a date or a decimal number. Once the statement runs, the table exists but is empty, ready to receive rows through INSERT. Almost every database project starts with a handful of these statements, which together describe the shape of your data.

Beyond names and types, a column definition can carry rules. NOT NULL forbids missing values, DEFAULT supplies a value when none is given, PRIMARY KEY identifies each row, UNIQUE blocks duplicates, CHECK enforces a condition and FOREIGN KEY ties a column to another table. Putting these rules in the table definition means the database protects your data no matter which application or analyst writes to it, which is far more reliable than hoping every query behaves.

You will also see CREATE TABLE used to copy data. Most databases let you build a new table from the result of a query, which is handy for snapshots, staging tables and intermediate analysis steps. The exact spelling of that feature differs by dialect, so the dialect notes below show each form. If you later need to add or change columns on an existing table, use ALTER TABLE rather than dropping and recreating it.

Basic syntax

CREATE TABLE [IF NOT EXISTS] table_name (
  column1 datatype [column_constraint ...],
  column2 datatype [column_constraint ...],
  ...
  [table_constraint ...]
);

-- Create a table from a query result
CREATE TABLE new_table AS
SELECT column1, column2
FROM existing_table
WHERE condition;

Each column is a name followed by a data type and optional constraints, separated by commas, with no trailing comma after the last item. Table constraints such as a composite primary key or a foreign key go after the column list. IF NOT EXISTS makes the statement do nothing when the table is already there, instead of raising an error.

SQL CREATE TABLE examples

A basic table with a primary key

This creates a customers table with an integer key, required name and email columns, and a signup date that defaults to today. The primary key guarantees each customer row can be identified uniquely, and NOT NULL stops incomplete rows from being stored.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  first_name  VARCHAR(50)  NOT NULL,
  last_name   VARCHAR(50)  NOT NULL,
  email       VARCHAR(255) NOT NULL UNIQUE,
  signup_date DATE         DEFAULT CURRENT_DATE
);

A table with a foreign key and a check

Orders reference customers through a foreign key, so you cannot store an order for a customer who does not exist. The CHECK constraint rejects negative totals, and the default status keeps new rows consistent without every insert needing to set it.

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER       NOT NULL,
  order_date  DATE          NOT NULL,
  total       DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  status      VARCHAR(20)   NOT NULL DEFAULT 'pending',
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

A composite primary key

A junction table linking orders and products has no single natural key, so the pair of columns together forms the primary key. Declaring it at table level, after the columns, is how you express a key that spans more than one column.

CREATE TABLE order_items (
  order_id   INTEGER       NOT NULL,
  product_id INTEGER       NOT NULL,
  quantity   INTEGER       NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Create a table from a query

This copies recent high-value orders into a new table in one step. The new columns take their names and types from the query, which is convenient for snapshots, but constraints such as primary keys are generally not copied and must be added afterwards.

CREATE TABLE big_orders_2026 AS
SELECT order_id, customer_id, order_date, total
FROM orders
WHERE total > 1000
  AND order_date >= '2026-01-01';

Where SQL dialects differ

PostgreSQL: Supports IF NOT EXISTS, CREATE TABLE ... AS SELECT, and identity columns with GENERATED ALWAYS AS IDENTITY (version 10 and later) or the older SERIAL shorthand. CREATE TEMP TABLE makes a session-scoped table.

MySQL: Uses AUTO_INCREMENT for generated keys and lets you choose a storage engine, normally InnoDB. IF NOT EXISTS and CREATE TABLE ... AS SELECT both work. CHECK constraints are only enforced from MySQL 8.0.16; earlier versions parse and ignore them.

SQL Server: Uses IDENTITY(1,1) for generated keys. There is no CREATE TABLE ... AS; use SELECT ... INTO new_table FROM ... instead. IF NOT EXISTS for tables is not available in the statement itself, so check OBJECT_ID first. Temporary tables start with #.

SQLite: Types are flexible and map to affinities, so VARCHAR(50) does not enforce a length. An INTEGER PRIMARY KEY column becomes an alias for the rowid and auto-assigns values. Supports IF NOT EXISTS and CREATE TABLE ... AS SELECT. Foreign keys are only enforced after PRAGMA foreign_keys = ON.

Oracle: Supports CREATE TABLE ... AS SELECT but not IF NOT EXISTS before version 23c. Identity columns (GENERATED AS IDENTITY) arrived in 12c. Text columns normally use VARCHAR2 rather than VARCHAR.

Common mistakes

Leaving a trailing comma after the last column or constraint. Most databases reject this with a syntax error that points at the closing parenthesis.
Choosing a data type that is too small or too loose, such as storing money in a floating-point column. Use DECIMAL or NUMERIC for currency so rounding errors do not creep in.
Forgetting NOT NULL on columns that must always have a value, then discovering missing data months later. Decide required columns when you design the table.
Assuming a table created from a query keeps the source keys and indexes. It usually copies only column names and types, so add the primary key and indexes yourself.
Running CREATE TABLE for a name that already exists. Add IF NOT EXISTS where supported, or confirm the table is absent first.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator