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

- URL: https://betteranalyst.com/sql/create-table

SQL guide · Tables, views & databases (DDL)

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

- [SQL ALTER TABLE](https://betteranalyst.com/sql/alter-table)
- [SQL PRIMARY KEY](https://betteranalyst.com/sql/primary-key)
- [SQL FOREIGN KEY](https://betteranalyst.com/sql/foreign-key)
- [SQL NOT NULL](https://betteranalyst.com/sql/not-null)
- [SQL DEFAULT](https://betteranalyst.com/sql/default)
- [SQL DROP TABLE](https://betteranalyst.com/sql/drop-table)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)