CREATE TABLE & Data Types

Reviewed & published by Brayan K

So far you've queried tables someone else built. Now you become the architect. CREATE TABLE defines a new table's columns and the data type each one holds — the foundation every other SQL command stands on.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

By the end you'll know the everyday data types, how to pick the right one (especially for money), and how to add NOT NULL, DEFAULT, and a PRIMARY KEY as you create a table.

What You'll Learn

• Write a CREATE TABLE statement
• Choose between INTEGER, VARCHAR, TEXT, DECIMAL, BOOLEAN, DATE
• Why DECIMAL — not FLOAT — for money
• Add NOT NULL and DEFAULT column options
• Declare a PRIMARY KEY and auto-increment ids
• Use IF NOT EXISTS and CREATE TABLE AS SELECT

The Most Common Data Types

A column's type decides what it can store and how much space it takes. Keep this cheat sheet handy.

1. Your First CREATE TABLE

The pattern is CREATE TABLE name (column type, column type, …). Each line names a column and the type of data it will hold. The table starts empty — you add rows later with INSERT.

Creating a table is like printing a blank spreadsheet with labelled, typed columns: "this column only accepts dates, this one only whole numbers". The rules are set before any data arrives.

-- The simplest CREATE TABLE: a name, then columns with types.
CREATE TABLE customers (
    id         INTEGER,
    full_name  VARCHAR(100),
    email      VARCHAR(255),
    signup_date DATE
);

-- CREATE TABLE prints nothing — it just makes an empty table. To see that it
-- worked, put a row in and read it back.
INSERT INTO customers VALUES (1, 'Ada Lovelace', 'ada@example.com', '2026-03-14');

SELECT * FROM customers;

-- ✅ Expected output:
-- id | full_name | email | signup_date
-- 1 | Ada Lovelace | ada@example.com | 2026-03-14

2. A Tour of Data Types

Choosing the right type matters for correctness and storage. The golden rule: use DECIMAL for money. REAL/FLOAT are approximate and introduce rounding errors that are unacceptable for currency.

-- A tour of the most common data types.
CREATE TABLE products (
    id           INTEGER,        -- whole numbers
    sku          CHAR(8),        -- fixed-length text, always 8 chars
    name         VARCHAR(120),   -- variable text up to 120 chars
    description  TEXT,           -- long, unbounded text
    price        DECIMAL(8,2),   -- exact money: up to 999999.99
    weight_kg    REAL,           -- approximate floating-point number
    in_stock     BOOLEAN,        -- true / false
    added_at     TIMESTAMP       -- date + time
);

INSERT INTO products VALUES
  (1, 'KB-00291', 'Mechanical keyboard', 'Tactile switches, 87 keys',
   129.99, 0.98, TRUE, '2026-03-14 09:30:00');

SELECT id, sku, name, price, weight_kg, in_stock FROM products;

-- Note what comes back for in_stock: 1, not "true". SQLite has no separate
-- boolean type and stores TRUE/FALSE as 1/0. Postgres and MySQL print true.
-- Use DECIMAL for money — never REAL/FLOAT (rounding errors!).

-- ✅ Expected output:
-- id | sku | name | price | weight_kg | in_stock
-- 1 | KB-00291 | Mechanical keyboard | 129.99 | 0.98 | 1

3. NOT NULL and DEFAULT

Two column options you'll use constantly: NOT NULL forces every row to supply a value, and DEFAULT fills in a value when you don't. Together they keep your data tidy from day one.

-- NOT NULL forces a value; DEFAULT supplies one when you omit the column.
CREATE TABLE accounts (
    id         INTEGER,
    username   VARCHAR(40)  NOT NULL,          -- must always be provided
    is_active  BOOLEAN      DEFAULT TRUE,       -- defaults to true
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    bio        TEXT                              -- nullable (optional)
);

-- Insert only id and username; the other three columns fill themselves.
INSERT INTO accounts (id, username) VALUES (1, 'ava');

-- created_at is a reading of the clock, so it is different every time you run
-- this. Checking that it is NOT NULL proves the default fired without claiming
-- a timestamp that cannot be true twice.
SELECT id, username, is_active, bio, created_at IS NOT NULL AS has_timestamp
FROM accounts;

-- ✅ Expected output:
-- id | username | is_active | bio | has_timestamp
-- 1 | ava | 1 | NULL | 1

4. Adding a PRIMARY KEY

Every table should have a PRIMARY KEY — a column whose value uniquely identifies each row. It's automatically NOT NULL and unique. Auto-incrementing ids are the most common choice, though the keyword differs by engine.

-- PRIMARY KEY: a column that uniquely identifies each row.
CREATE TABLE users (
    id        INTEGER PRIMARY KEY,   -- unique, not null, the row's identity
    email     VARCHAR(255) NOT NULL,
    name      VARCHAR(100)
);

INSERT INTO users VALUES (1, 'ada@example.com', 'Ada');
INSERT INTO users VALUES (2, 'alan@example.com', 'Alan');

SELECT * FROM users ORDER BY id;

-- Try adding a third row with id 1 and the insert is rejected: that is the
-- primary key doing its job.
--
-- Auto-numbering keywords differ by engine:
--   Postgres: id SERIAL PRIMARY KEY
--   MySQL:    id INT AUTO_INCREMENT PRIMARY KEY
--   SQLite:   id INTEGER PRIMARY KEY  (auto-rowid)

-- ✅ Expected output:
-- id | email | name
-- 1 | ada@example.com | Ada
-- 2 | alan@example.com | Alan

Your Turn: a tasks table

Fill in the blanks to create a small tasks table with a primary key, a required title, and a default flag.

-- 🎯 YOUR TURN — fill in the blanks
-- Goal: a "tasks" table with an integer id primary key, a required
-- title (max 200 chars), and a "done" flag defaulting to false.

CREATE TABLE tasks (
    id     INTEGER ____ KEY,        -- 👉 PRIMARY
    title  VARCHAR(200) ____ ,      -- 👉 NOT NULL
    done   BOOLEAN DEFAULT ____     -- 👉 FALSE
);

-- ✅ Expected: an empty 3-column table; inserting a row without "done"
--    stores done = false.

5. IF NOT EXISTS

Re-running a setup script shouldn't crash. CREATE TABLE IF NOT EXISTS creates the table only if it's missing, turning a duplicate run into a harmless no-op.

-- IF NOT EXISTS avoids an error if the table is already there.
CREATE TABLE IF NOT EXISTS settings (
    key   VARCHAR(50) PRIMARY KEY,
    value TEXT
);

-- Run the very same statement again. Without IF NOT EXISTS this second one
-- would fail with "table settings already exists"; here it is a harmless
-- no-op, which is what makes setup scripts safe to re-run.
CREATE TABLE IF NOT EXISTS settings (
    key   VARCHAR(50) PRIMARY KEY,
    value TEXT
);

INSERT INTO settings VALUES ('theme', 'dark');
SELECT * FROM settings;

-- ✅ Expected output:
-- key | value
-- theme | dark

6. CREATE TABLE AS SELECT

You can build a table directly from a query's results — handy for snapshots and quick backups. The new table inherits the columns and data, though usually not the keys or constraints.

-- You can also build a table from a query (a quick snapshot/backup).
-- First, the table this lesson queries — a snippet should create what it reads.
CREATE TABLE products (
    id INTEGER, product_name VARCHAR(120), category VARCHAR(40), price DECIMAL(8,2)
);
INSERT INTO products VALUES
  (1, 'Mechanical keyboard', 'Electronics', 129.99),
  (2, 'Standing desk',       'Furniture',   349.00),
  (3, 'USB-C hub',           'Electronics',  59.50);

CREATE TABLE electronics AS
SELECT id, product_name, price
FROM products
WHERE category = 'Electronics';

SELECT * FROM electronics ORDER BY id;

-- The new table inherits the result's columns and data.
-- Note: this copies the DATA but usually NOT keys/constraints.
-- 59.5 rather than 59.50 is your engine printing the number, not losing it.

-- ✅ Expected output:
-- id | product_name | price
-- 1 | Mechanical keyboard | 129.99
-- 3 | USB-C hub | 59.5

Reorder Challenge

These lines of a CREATE TABLE statement are scrambled. Put them in the correct order:

A.   email VARCHAR(255) NOT NULL,
B.   CREATE TABLE users (
C.   );
D.   id INTEGER PRIMARY KEY,
E.   name VARCHAR(100)

Quick Recall

1) Which type should store a product price like 19.99?

DECIMAL(p, s) — exact base-10. Never FLOAT/REAL, which round and would corrupt money totals.

2) You insert a row but omit a DEFAULT TRUE column. What is stored?

TRUE. DEFAULT supplies the value when you don't provide one.

3) What does PRIMARY KEY imply about a column besides uniqueness?

It's automatically NOT NULL — a primary key can never be empty, since it identifies the row.

Common Errors (and the fix)

• Trailing comma: a comma after the last column before ) is a syntax error. Remove it.
• "table already exists": use CREATE TABLE IF NOT EXISTS or drop it first.
• FLOAT for money: rounding errors creep in. Use DECIMAL.
• Missing length on VARCHAR (some engines): MySQL needs VARCHAR(n); pick a sensible n.
• Reserved words as names: avoid columns named order, group, etc., or quote them carefully.

Mini-Challenge: A Books Table

Design a real table with the right type for each column. Write the CREATE TABLE, then run it to confirm it's valid.

-- 🎯 MINI-CHALLENGE
-- Design a "books" table:
--   - id: integer, primary key
--   - title: text up to 200 chars, required
--   - price: exact money with 2 decimal places
--   - published: a date
--   - in_print: boolean, defaults to true
--
-- ✅ Expected: a valid CREATE TABLE statement that runs without error.

-- your CREATE TABLE here

🎉 Lesson Complete

• ✅ CREATE TABLE name (col type, …) defines a new table
• ✅ Pick types deliberately — DECIMAL for money, never FLOAT
• ✅ NOT NULL requires values; DEFAULT supplies them
• ✅ A PRIMARY KEY is unique and automatically NOT NULL
• ✅ IF NOT EXISTS and CREATE TABLE AS SELECT are handy extras
• ✅ Next: ALTER TABLE — changing a table after it exists

Practice quiz

Continue this course

• Previous: UNION, INTERSECT & EXCEPT
• Next: ALTER TABLE — Add, drop and modify columns on existing tables
• Quick reference: SQL cheat sheet › Data Types
• From the blog: SQL Database Design Best Practices

Frequently asked questions

What's the difference between CHAR, VARCHAR, and TEXT?

CHAR(n) is fixed-length and pads shorter values with spaces — good for codes that are always the same length (like a 2-letter country code). VARCHAR(n) is variable-length up to a limit and only stores what you put in. TEXT is for long, unbounded strings like articles or comments. Use VARCHAR for most short strings and TEXT for large free-form content.

Why use DECIMAL for money instead of FLOAT or REAL?

FLOAT and REAL are approximate binary floating-point types, so values like 0.1 can't be stored exactly and arithmetic accumulates tiny rounding errors. DECIMAL(p, s) stores exact base-10 values with a fixed number of decimal places, which is essential for currency. Always use DECIMAL (or NUMERIC) for prices and balances.

What does NOT NULL actually do?

NOT NULL is a constraint that forbids the column from holding NULL (the absence of a value). Any INSERT or UPDATE that would leave it empty is rejected. Use it for columns that must always have a value, like a username or an order total. Columns without NOT NULL are optional and may be NULL.

How do I make an id auto-increment?

It varies by engine: PostgreSQL uses SERIAL (or GENERATED ALWAYS AS IDENTITY), MySQL uses INT AUTO_INCREMENT, and SQLite auto-generates rowids for an INTEGER PRIMARY KEY. All three give each new row a unique, automatically growing id so you don't have to supply it yourself.