SQL CREATE TABLE Guide

CREATE TABLE adds a new table to a database. You name the table and list its columns, each with a data type and any rules (constraints) its values have to follow. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.

How to use CREATE TABLE

Name the table, then list its columns in parentheses, separated by commas. Each column gets a name, a data type and, optionally, constraints such as PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT or CHECK:

CREATE TABLE table_name (
    column_name data_type constraints,
    another_column data_type constraints
);

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    country CHAR(2) DEFAULT 'US',
    signed_up DATE
);

INSERT INTO customers (id, name, email, signed_up)
VALUES (1, 'Ana Lima', 'ana@example.com', '2026-03-02');

SELECT * FROM customers;

-- Output:
-- id | name     | email           | country | signed_up
-- ---+----------+-----------------+---------+-----------
-- 1  | Ana Lima | ana@example.com | US      | 2026-03-02

The new table is empty until you insert rows into it. This insert left out country, so the row got the default, US.

Rules to know

• SQLite doesn’t enforce types or lengths. Where other databases reject a value that doesn’t fit the column, SQLite stores it anyway. Add STRICT after the closing parenthesis to make it check (a strict table only takes the types INT, INTEGER, REAL, TEXT, BLOB and ANY).

CREATE TABLE products (
    sku VARCHAR(5),
    quantity INTEGER
);

-- SQLite stores this row; other databases reject it with an error
INSERT INTO products VALUES ('LAMP-001', 'twelve');

SELECT sku, quantity FROM products;

-- Output (SQLite):
-- sku      | quantity
-- ---------+---------
-- LAMP-001 | twelve

• Declare a foreign key on its own FOREIGN KEY line, not as REFERENCES inside the column definition: MySQL 8 accepts that shorter form and then ignores it. SQLite only enforces foreign keys after PRAGMA foreign_keys = ON;, which you run on each connection.
• CREATE TABLE fails if the table already exists. Write CREATE TABLE IF NOT EXISTS to skip it instead.
• In MySQL, a TEXT column can’t have a DEFAULT or UNIQUE. Use VARCHAR(n), as the examples here do.

Alternative syntax

Auto-numbered ids are written differently in each database:

Leave the id out of an insert and the database fills in the next number.

SQL Server also has its own names for true/false and date-time columns, and no IF NOT EXISTS. Use BIT (with 1 and 0) instead of BOOLEAN, DATETIME2 for a date and time (its TIMESTAMP is a row version number, not a date), and check OBJECT_ID before creating the table:

-- SQL Server
IF OBJECT_ID('users', 'U') IS NULL
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    is_active BIT NOT NULL DEFAULT 1,
    created_at DATETIME2 NOT NULL DEFAULT CURRENT_TIMESTAMP
);

When should you use CREATE TABLE?

Every table starts with CREATE TABLE, but the columns and constraints you choose solve different problems.

Store a new kind of data

Each kind of thing your application keeps track of, such as customers, books or invoices, gets its own table, with a column for each fact about it and a type that fits the values.

Give every row a unique ID

A PRIMARY KEY stops two rows from sharing an identifier, so you can always find, update or link to exactly one row. Let the database number the rows (see “Alternative syntax” above), or combine two columns when it takes a pair of values to identify a row.

Keep bad or missing data out

Constraints reject rows that break your rules: NOT NULL for required columns, UNIQUE for values such as email addresses, and CHECK for allowed ranges. DEFAULT fills in a column that an insert leaves out.

Link related tables

A FOREIGN KEY makes a column point at a row in another table, so an order can’t name a customer who doesn’t exist. Add ON DELETE CASCADE and deleting the parent row deletes the rows that point at it too.

Save query results as a table

CREATE TABLE ... AS SELECT creates a table and fills it from a query in one statement. Use it for snapshots, report tables, or a backup of rows you’re about to change.

Real examples of CREATE TABLE

The error messages are PostgreSQL’s. Other databases reject the same rows with their own wording.

A link table with a two-column key

Students take many courses, and courses have many students. The enrollments table links them, and its primary key is the pair of ids, so a student can join many courses but can’t join the same course twice:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_on DATE,
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments VALUES (1, 101, '2026-09-01');
INSERT INTO enrollments VALUES (1, 102, '2026-09-01');
INSERT INTO enrollments VALUES (1, 101, '2026-09-08');
-- ERROR: duplicate key value violates unique constraint "enrollments_pkey"
-- DETAIL: Key (student_id, course_id)=(1, 101) already exists.

SELECT * FROM enrollments;

-- Output:
-- student_id | course_id | enrolled_on
-- -----------+-----------+------------
-- 1          | 101       | 2026-09-01
-- 1          | 102       | 2026-09-01

Sign-ups that follow the rules

Every account needs an email address that no other account uses and at least one seat. New accounts start on the free tier with one seat:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    tier VARCHAR(20) NOT NULL DEFAULT 'free',
    seats INTEGER NOT NULL DEFAULT 1 CHECK (seats > 0)
);

INSERT INTO accounts (id, email) VALUES (1, 'sam@example.com');

SELECT * FROM accounts;

-- Output:
-- id | email           | tier | seats
-- ---+-----------------+------+------
-- 1  | sam@example.com | free | 1

INSERT INTO accounts (id, email) VALUES (2, 'sam@example.com');
-- ERROR: duplicate key value violates unique constraint "accounts_email_key"

INSERT INTO accounts (id, email, seats) VALUES (3, 'kim@example.com', 0);
-- ERROR: new row ... violates check constraint "accounts_seats_check"

INSERT INTO accounts (id) VALUES (4);
-- ERROR: null value in column "email" ... violates not-null constraint

Orders that must belong to a customer

Each order points at a customer. The foreign key rejects an order for a customer who doesn’t exist, and ON DELETE CASCADE removes a customer’s orders when the customer is deleted. In SQLite, run PRAGMA foreign_keys = ON; first.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- Input: customers
-- id | name
-- ---+-----------
-- 1  | Ana Lima
-- 2  | Ben Okafor

-- Input: orders
-- id | customer_id | total
-- ---+-------------+------
-- 10 | 1           | 25.00
-- 11 | 1           | 60.00
-- 12 | 2           | 42.50

INSERT INTO orders VALUES (13, 99, 15.00);
-- ERROR: ... violates foreign key constraint "orders_customer_id_fkey"
-- DETAIL: Key (customer_id)=(99) is not present in table "customers".

DELETE FROM customers WHERE id = 1;

-- Output (orders after the DELETE):
-- id | customer_id | total
-- ---+-------------+------
-- 12 | 2           | 42.50

A snapshot of active customers

Copy the customers who ordered this year into their own table, for a campaign list that shouldn’t change as new orders come in:

-- Input: customers
-- customer_id | name       | email             | last_order_date
-- ------------+------------+-------------------+----------------
-- 1           | Ana Lima   | ana@example.com   | 2026-03-18
-- 2           | Ben Okafor | ben@example.com   | 2025-11-02
-- 3           | Chloe Park | chloe@example.com | 2026-06-30
-- 4           | Dev Shah   | dev@example.com   | NULL

CREATE TABLE active_customers AS
SELECT customer_id, name, email
FROM customers
WHERE last_order_date >= '2026-01-01';

-- SQL Server has no CREATE TABLE ... AS. Use SELECT ... INTO:
SELECT customer_id, name, email
INTO active_customers
FROM customers
WHERE last_order_date >= '2026-01-01';

-- Output (active_customers):
-- customer_id | name       | email
-- ------------+------------+------------------
-- 1           | Ana Lima   | ana@example.com
-- 3           | Chloe Park | chloe@example.com

The new table gets the columns, their types and the rows, but not the primary key or any other constraint. Add those with ALTER TABLE if you need them.

Further reading about CREATE TABLE

On this blog:

• SQL temp table guide: tables that only last as long as your session
• SQL ALTER TABLE add column: changing a table after you’ve created it
• Composite primary keys in SQL: keys made of more than one column
• How to use foreign keys in PostgreSQL: foreign keys and their delete actions in more depth
• Database relationships guide: designing tables that link together
• SQL INSERT statement: adding rows to a new table
• SQL table creator: build a CREATE TABLE statement in your browser instead of writing it by hand

Official documentation:

• PostgreSQL: CREATE TABLE
• MySQL: CREATE TABLE
• SQLite: CREATE TABLE
• SQL Server: CREATE TABLE