SQL CREATE TABLE and INSERT: Setting Up Your Data

Before you can query data you have to put it somewhere. CREATE TABLE and INSERT are the two statements that turn an empty database into a useful one. This post covers the parts you actually use in real schemas — types, constraints, and the careful use of ALTER and DROP.

If SELECT was about asking questions, this post is about giving the database something worth asking about.

The running dataset

We’ll build the same users and orders schema used throughout the SQL series, plus a small products table.

CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE,
  country    TEXT,
  age        INTEGER,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

We’ll dissect every line of that statement below.

The shape of CREATE TABLE

The skeleton is straightforward:

CREATE TABLE table_name (
  column_name column_type [constraints],
  column_name column_type [constraints],
  ...
);

Each line declares one column with three pieces:

1. Name — lowercase with underscores by convention.
2. Type — what kind of values the column holds.
3. Constraints — rules the database enforces on every row.

The column list lives inside parentheses and ends with a semicolon. The trailing comma after the last column is not allowed in standard SQL.

Choosing column types

Every column needs a type. Pick the narrowest type that comfortably holds your data — narrow types are faster, take less storage, and catch bugs that a wider type would silently accept.

Numbers

• INTEGER — whole numbers. The default integer is plenty for IDs, ages, counts.
• BIGINT — for IDs that may exceed 2 billion.
• REAL / DOUBLE PRECISION — floating-point numbers. Fine for measurements; avoid for money.
• NUMERIC(precision, scale) — exact decimal numbers. Use for currency: NUMERIC(10, 2) holds values up to 99,999,999.99.

Text

• TEXT — variable-length string of any length. Postgres and SQLite encourage TEXT.
• VARCHAR(n) — variable-length string capped at n characters. MySQL leans on VARCHAR.
• CHAR(n) — fixed-length string padded with spaces. Rarely useful in modern code.

Use TEXT when you don’t have a strong reason to cap length. Use VARCHAR(n) when there’s a real constraint — a country code is always two characters, a US state abbreviation is two.

Booleans

• BOOLEAN — TRUE or FALSE. SQLite stores these as 0/1, which is fine.

Dates and times

• DATE — calendar date, no time.
• TIME — clock time, no date.
• TIMESTAMP — date and time. Use TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ in Postgres) for anything user-facing.

JSON

Modern databases support a JSON (or JSONB in Postgres) column for semi-structured blobs. Reach for it when the shape genuinely varies; otherwise normal columns are faster and stricter.

Constraints

Constraints are rules the database enforces on every row. They are the cheapest, most reliable form of data validation you’ll ever have.

NOT NULL

NOT NULL rejects rows that leave the column blank. Apply it to anything that should always have a value.

name TEXT NOT NULL

The default — when you omit NOT NULL — is that the column does allow NULL. This catches beginners constantly. Be explicit.

PRIMARY KEY

A primary key is the column (or set of columns) that uniquely identifies a row. It implies NOT NULL and UNIQUE.

id INTEGER PRIMARY KEY

In SQLite, INTEGER PRIMARY KEY auto-assigns an incrementing value when you omit it. Postgres uses SERIAL or, more modernly, GENERATED ALWAYS AS IDENTITY. MySQL uses AUTO_INCREMENT. Every table you create should have one.

UNIQUE

UNIQUE enforces that no two rows share the same value in that column.

email TEXT NOT NULL UNIQUE

A UNIQUE constraint is a promise: the database will reject an INSERT that violates it. Trust this and lean on it — don’t paper over duplicates in application code.

DEFAULT

DEFAULT provides a value when the INSERT doesn’t supply one.

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
status     TEXT NOT NULL DEFAULT 'pending'

Defaults are perfect for “record bookkeeping” columns: created timestamps, status fields with an obvious initial value, flags that start at zero.

REFERENCES (foreign keys)

A foreign key says “this column points to a row in another table.” It prevents orphaned data.

CREATE TABLE orders (
  id       INTEGER PRIMARY KEY,
  user_id  INTEGER NOT NULL REFERENCES users(id),
  total    NUMERIC(10, 2) NOT NULL,
  status   TEXT NOT NULL DEFAULT 'pending'
);

We’ll exercise foreign keys properly in SQL JOINs.

INSERT INTO

INSERT INTO adds rows. The full form names every column you’re providing, then lists values in the same order:

INSERT INTO users (id, name, email, country, age) VALUES
  (1, 'Ada', 'ada@example.com', 'UK', 36);

You can omit columns that have a default or allow NULL:

INSERT INTO users (name, email) VALUES
  ('Alan', 'alan@example.com');

id is filled by the primary-key auto-increment, created_at by the default, and country / age are left NULL.

Multi-row INSERT

You can insert many rows in a single statement. This is faster than running one INSERT per row, and it’s atomic — either every row lands or none of them do.

INSERT INTO users (id, name, email, country, age) VALUES
  (1, 'Ada',      'ada@example.com',      'UK', 36),
  (2, 'Linus',    'linus@example.com',    'FI', 55),
  (3, 'Grace',    'grace@example.com',    'US', 41),
  (4, 'Margaret', 'margaret@example.com', 'US', 29),
  (5, 'Dennis',   'dennis@example.com',   'UK', 62);

Commas separate the tuples, a single semicolon ends the statement.

Skipping the column list

You can skip the column list and rely on declaration order:

INSERT INTO users VALUES (6, 'Edsger', 'ed@example.com', 'NL', 75, CURRENT_TIMESTAMP);

Don’t. The column list is what keeps your INSERTs working when the schema changes. Always name your columns.

The products table

To round out the running dataset:

CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  price    NUMERIC(10, 2) NOT NULL,
  in_stock BOOLEAN NOT NULL DEFAULT TRUE
);

INSERT INTO products (id, name, price, in_stock) VALUES
  (1, 'Notebook',   12.50, TRUE),
  (2, 'Pen',         2.00, TRUE),
  (3, 'Desk Lamp',  49.00, TRUE),
  (4, 'Whiteboard', 89.00, FALSE);

NUMERIC(10, 2) keeps prices exact — no floating-point surprises when you sum them.

DROP TABLE

DROP TABLE permanently removes a table and every row in it. It is irreversible outside of a backup.

DROP TABLE products;
DROP TABLE IF EXISTS products;

The IF EXISTS variant is the friendly form — no error if the table is already gone. Use it in setup scripts; avoid the unguarded form in anything that touches production.

ALTER TABLE

ALTER TABLE changes an existing table without losing its data. The common variations:

-- Add a column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Drop a column (Postgres / MySQL)
ALTER TABLE users DROP COLUMN phone;

-- Rename a column
ALTER TABLE users RENAME COLUMN phone TO mobile;

-- Rename the table
ALTER TABLE users RENAME TO accounts;

When you add a column with NOT NULL you need to either give it a DEFAULT or backfill values for existing rows before adding the constraint. Otherwise the existing rows have no value and the constraint is impossible to satisfy.

ALTER TABLE users ADD COLUMN signup_source TEXT NOT NULL DEFAULT 'unknown';

SQLite supports a narrower subset of ALTER than Postgres or MySQL — if you hit a limit, the workaround is to create a new table, copy the data, drop the old one, and rename.

A worked example

A small script that builds the entire running dataset from scratch:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS products;

CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE,
  country    TEXT,
  age        INTEGER,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  price    NUMERIC(10, 2) NOT NULL,
  in_stock BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL DEFAULT 1,
  total      NUMERIC(10, 2) NOT NULL,
  status     TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO users (id, name, email, country, age) VALUES
  (1, 'Ada',      'ada@example.com',      'UK', 36),
  (2, 'Linus',    'linus@example.com',    'FI', 55),
  (3, 'Grace',    'grace@example.com',    'US', 41),
  (4, 'Margaret', 'margaret@example.com', 'US', 29),
  (5, 'Dennis',   'dennis@example.com',   'UK', 62);

INSERT INTO products (id, name, price, in_stock) VALUES
  (1, 'Notebook',   12.50, TRUE),
  (2, 'Pen',         2.00, TRUE),
  (3, 'Desk Lamp',  49.00, TRUE),
  (4, 'Whiteboard', 89.00, FALSE);

INSERT INTO orders (id, user_id, product_id, quantity, total, status) VALUES
  (101, 1, 1, 2,  25.00, 'paid'),
  (102, 1, 2, 5,  10.00, 'paid'),
  (103, 2, 3, 1,  49.00, 'refunded'),
  (104, 3, 4, 1,  89.00, 'paid'),
  (105, 4, 1, 1,  12.50, 'pending'),
  (106, 4, 2, 3,   6.00, 'paid'),
  (107, 5, 3, 2,  98.00, 'paid');

Run this once and every example in the rest of the series works against the same data.

Common beginner mistakes

• Forgetting NOT NULL and ending up with optional columns that should be required.
• Storing money in REAL or DOUBLE — use NUMERIC so arithmetic stays exact.
• Inserting without naming columns — works until someone reorders the schema.
• Using DROP TABLE without IF EXISTS in setup scripts and seeing the whole script fail.
• Adding a NOT NULL column to a populated table with no default — the database rejects it, and rightly so.

Recap

You now know:

• CREATE TABLE name (column type constraints, ...) defines a schema
• Pick the narrowest type that fits — INTEGER, TEXT, NUMERIC, BOOLEAN, TIMESTAMP
• PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, REFERENCES are the constraints you use every day
• INSERT INTO table (cols) VALUES (...) adds rows; multi-row inserts are atomic and fast
• DROP TABLE IF EXISTS and ALTER TABLE change schemas after the fact

A well-shaped schema is the most important thing you build. Every query later either celebrates or curses the choices you made here.

Next steps

With data in two or three related tables, the next obvious question is how to combine them. That’s what JOIN is for.

Next: SQL JOINs — INNER, LEFT, RIGHT, and FULL Explained

Related reading: What Is SQL?, SQL SELECT Basics.

Questions or feedback? Email codeloomdevv@gmail.com.