DDL

CREATE TABLE

Q: How do I make a column auto-increment?

Engine-specific. PostgreSQL: id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY (modern) or id BIGSERIAL PRIMARY KEY (legacy). MySQL: id INT AUTO_INCREMENT PRIMARY KEY. SQL Server: id INT IDENTITY(1,1) PRIMARY KEY. SQLite: id INTEGER PRIMARY KEY AUTOINCREMENT. BigQuery and Snowflake have different patterns — see PRIMARY KEY.

Q: What's the difference between PRIMARY KEY and UNIQUE?

A table has at most one PRIMARY KEY; it's implicitly NOT NULL and UNIQUE, and is the canonical row identifier. UNIQUE constraints can be applied to any number of columns and do allow NULL (multiple rows can have NULL in a UNIQUE column on most engines). Use PRIMARY KEY for "this is the row" and UNIQUE for "this is also unique business-wise."

Q: How do I create a table from a SELECT query?

Use CREATE TABLE AS SELECT (CTAS): CREATE TABLE active_users AS SELECT FROM users WHERE is_active = TRUE;. The new table inherits the column types from the query. Variant: CREATE TABLE shape AS SELECT FROM users WHERE 1 = 0; creates an empty clone with the same schema but no rows.

Q: How do I make CREATE TABLE re-runnable in a migration?

Add IF NOT EXISTS: CREATE TABLE IF NOT EXISTS users (...);. Supported on Postgres, MySQL, SQLite, DuckDB, Snowflake, BigQuery, SQL Server (2016+). The statement becomes a no-op if the table already exists, instead of erroring.

Q: What types should I use for money / currency columns?

DECIMAL(p, s) (or NUMERIC(p, s)), never FLOAT or DOUBLE. DECIMAL(15, 2) covers up to ~$10 trillion with cent precision. Use DECIMAL(15, 4) for per-unit pricing where you need 4-decimal precision. See DECIMAL vs FLOAT.

Define a new table: a name, a list of columns with types, and any constraints (primary key, not null, defaults, foreign keys). Most of the design decisions you make here are hard to undo cheaply once data lands, so the cost-of-mistake is highest on the first CREATE TABLE and falls quickly after.

Syntax #

sql

CREATE TABLE table_name (
  column_1 data_type [NOT NULL] [DEFAULT value] [PRIMARY KEY],
  column_2 data_type [NOT NULL] [DEFAULT value],
  ...
  [CONSTRAINT name PRIMARY KEY (column_1, ...)],
  [CONSTRAINT name FOREIGN KEY (column_x) REFERENCES other_table(other_col)]
);

Example #

Loading SQL editor...

Common variants #

sql

-- Idempotent: don't fail if the table already exists.
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, email VARCHAR);

-- CREATE TABLE AS SELECT (CTAS): clone shape and data from a query.
CREATE TABLE recent_users AS
SELECT * FROM users WHERE created_at >= DATE '2024-01-01';

-- Empty clone: shape only, no rows.
CREATE TABLE users_template AS
SELECT * FROM users WHERE 1 = 0;

-- Composite primary key.
CREATE TABLE order_items (
  order_id   INTEGER,
  line_no    INTEGER,
  product_id INTEGER NOT NULL,
  qty        INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

-- Temporary table: dropped at end of session.
CREATE TEMP TABLE staging_users (id INTEGER, email VARCHAR);

Dialect notes #

Auto-incrementing IDs: PostgreSQL uses SERIAL or GENERATED ALWAYS AS IDENTITY; MySQL uses AUTO_INCREMENT; SQLite uses INTEGER PRIMARY KEY AUTOINCREMENT; SQL Server uses IDENTITY(1,1); Snowflake/BigQuery typically don't use auto-increment, so surrogate keys come from sequences or hashing.
String types: ANSI says VARCHAR(n); Postgres tolerates unbounded VARCHAR and TEXT interchangeably; MySQL distinguishes VARCHAR vs TEXT based on length and indexing rules; warehouses (Snowflake, BigQuery) use STRING or unbounded VARCHAR.
Booleans: Native in Postgres, MySQL (alias for TINYINT), SQL Server (BIT); not native in Oracle (use NUMBER(1) or CHAR(1)); BigQuery uses BOOL.
Decimals for money: Always DECIMAL(p, s) / NUMERIC(p, s), never FLOAT / DOUBLE. Floats round in surprising places that you find at month-end close.

FAQ #

What's the basic syntax for CREATE TABLE in SQL?

CREATE TABLE table_name (column_1 data_type [constraints], column_2 data_type [constraints], ...);. For example, CREATE TABLE users (id INTEGER PRIMARY KEY, email VARCHAR NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);. Add IF NOT EXISTS to make the statement re-runnable.

How do I make a column auto-increment?

Engine-specific. PostgreSQL: id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY (modern) or id BIGSERIAL PRIMARY KEY (legacy). MySQL: id INT AUTO_INCREMENT PRIMARY KEY. SQL Server: id INT IDENTITY(1,1) PRIMARY KEY. SQLite: id INTEGER PRIMARY KEY AUTOINCREMENT. BigQuery and Snowflake have different patterns — see PRIMARY KEY.

What's the difference between PRIMARY KEY and UNIQUE?

A table has at most one PRIMARY KEY; it's implicitly NOT NULL and UNIQUE, and is the canonical row identifier. UNIQUE constraints can be applied to any number of columns and do allow NULL (multiple rows can have NULL in a UNIQUE column on most engines). Use PRIMARY KEY for "this is the row" and UNIQUE for "this is also unique business-wise."

Should I use VARCHAR or TEXT for string columns?

On PostgreSQL: they're stored identically — same performance, same storage. Use VARCHAR(n) only when the length cap is part of the data contract (e.g., country code = VARCHAR(2)). On MySQL: VARCHAR is row-inline, TEXT is stored separately and cannot be fully indexed — use VARCHAR for indexed/queried columns, TEXT for body text. See VARCHAR vs TEXT.

How do I create a table from a SELECT query?

Use CREATE TABLE AS SELECT (CTAS): CREATE TABLE active_users AS SELECT FROM users WHERE is_active = TRUE;. The new table inherits the column types from the query. Variant: CREATE TABLE shape AS SELECT FROM users WHERE 1 = 0; creates an empty clone with the same schema but no rows.

How do I make CREATE TABLE re-runnable in a migration?

Add IF NOT EXISTS: CREATE TABLE IF NOT EXISTS users (...);. Supported on Postgres, MySQL, SQLite, DuckDB, Snowflake, BigQuery, SQL Server (2016+). The statement becomes a no-op if the table already exists, instead of erroring.

What types should I use for money / currency columns?

DECIMAL(p, s) (or NUMERIC(p, s)), never FLOAT or DOUBLE. DECIMAL(15, 2) covers up to ~$10 trillion with cent precision. Use DECIMAL(15, 4) for per-unit pricing where you need 4-decimal precision. See DECIMAL vs FLOAT.

Warning

Schema decisions outlive code decisions. Renaming a column, changing a type, or splitting a table after a billion rows have landed is an order of magnitude more painful than getting it right up front. Before you ship a new CREATE TABLE: confirm the natural key (or that you genuinely need a surrogate), pick NOT NULL aggressively (every nullable column is a future COALESCE), and use DECIMAL not FLOAT for any value that represents money.