SQL CREATE TABLE: How to Create and Alter Tables

The SQL CREATE TABLE statement defines a table, its columns, and the data each column should hold. It can also enforce rules such as required values and unique identifiers.

The examples use SQLite, whose flexible type system differs from stricter database engines.

This article comes from the "Tables" chapter of Learn SQL, where you can run each query against a real database.

SQL CREATE TABLE Syntax

To create a new table in a database, use the CREATE TABLE statement followed by the name of the table and the fields you want in the table.

CREATE TABLE employees (id INTEGER, name TEXT, age INTEGER, is_manager BOOLEAN, salary INTEGER);

Each field name is followed by its datatype. We'll get to data types in a minute.

It's also acceptable and common to break up the CREATE TABLE statement with some whitespace like this:

CREATE TABLE employees(
  id INTEGER,
  name TEXT,
  age INTEGER,
  is_manager BOOLEAN,
  salary INTEGER
);

While INT and INTEGER are functionally identical, INTEGER is the fully standard-compliant keyword. We prefer INTEGER for clarity, consistency, and better portability across SQL dialects.

Column definitions can also include constraints after the type to add rules:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  image_url TEXT,
  description TEXT NOT NULL,
  author_id INTEGER NOT NULL,
  is_sponsored BOOLEAN NOT NULL DEFAULT false
);

• PRIMARY KEY uniquely identifies each row. In SQLite, a column declared exactly INTEGER PRIMARY KEY is an alias for the table's internal rowid.
• NOT NULL rejects missing values for that column.
• DEFAULT supplies a value when an insert omits the column.

Integer IDs are simple and efficient. UUIDs are useful when separate systems must generate IDs.

When to Use CREATE TABLE IF NOT EXISTS

SQLite supports CREATE TABLE IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS posts (
  id INTEGER PRIMARY KEY,
  description TEXT NOT NULL
);

Without IF NOT EXISTS, SQLite returns an error when a table or view with the same name already exists. With it, SQLite leaves the existing object alone.

SQLite doesn't check the existing table against the new definition. IF NOT EXISTS is handy for disposable setup scripts, but it can hide an outdated schema. Use explicit, versioned changes for application databases.

In most relational databases, a single table isn't enough to hold all the data we need! We usually create a table-per-entity. For example, a social media application might have the following tables:

• users
• posts
• comments
• likes

Choosing SQLite Data Types

SQL as a language can support many different data types. However, the types that your database management system (DBMS) supports will depend on which database you choose. As for SQLite, it supports only the most basic types.

Let's go over the data types supported by SQLite and how they're stored. Each is a storage class, which describes how SQLite stores an individual value.

1. NULL – Null value.
2. INTEGER – Signed integer stored in 0, 1, 2, 3, 4, 6, or 8 bytes.
3. REAL – Floating-point value stored as a 64-bit IEEE floating-point number.
4. TEXT – Text string stored using the database encoding, most commonly UTF-8.
5. BLOB – Short for Binary Large Object and typically used for images, audio, or other multimedia.
6. BOOLEAN – Boolean values are written in SQLite queries as true or false, but are recorded as 1 or 0.

It's important to note, SQLite does not have a separate BOOLEAN storage class. Instead, boolean values are stored as integers:

• 0 = false
• 1 = true

It's not actually all that weird – boolean values are just binary bits after all!

SQLite will let you write your queries using boolean expressions and true/false keywords, but it will convert the booleans to integers under-the-hood. Declaring a column as BOOLEAN gives it NUMERIC affinity rather than creating a new Boolean type.

Dates and times don't have a dedicated storage class either. SQLite can represent them as ISO-8601 TEXT, Julian day REAL, or Unix timestamp INTEGER values. Pick one representation and stick to it.

When it comes to money, to avoid problems with floating-point math, the best practice is to use INTEGER for currency amounts. The value then represents the smallest denomination. For example, $42.67 would be stored as 4267 (cents).

Does SQLite Enforce Column Types?

Normal SQLite tables use dynamic typing: the type belongs to the value, not its container. A declared column type gives the column a type affinity, which tells SQLite which storage class to prefer but usually doesn't prohibit others.

For example, SQLite will try to convert numeric text inserted into an INTEGER column to an integer. If conversion isn't possible, a normal table may still store the text value in that column.

SQLite also supports STRICT tables when you need rigid type enforcement:

CREATE TABLE players (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  level INTEGER NOT NULL
) STRICT;

Strict tables accept a small set of declared type names and reject values that can't be losslessly converted to the declared type. Other database engines have their own type systems, so don't assume SQLite's rules apply everywhere.

Inspecting a Table in SQLite

If you're curious, the PRAGMA TABLE_INFO(TABLENAME); command returns information about a table and its fields:

PRAGMA table_info(posts);

It returns one row per column, including its name, declared type, NOT NULL flag, default value, and primary-key position. Use it after running schema SQL to see the structure SQLite recorded.

For generated or hidden columns, use PRAGMA table_xinfo(posts) instead. The broader sqlite_schema table stores the SQL text for tables, indexes, views, and triggers.

Altering an Existing SQL Table

We often need to alter our database schema without deleting it and re-creating it. Imagine if Twitter deleted its database each time it needed to add a feature, that would be a disaster! Your account and all your tweets would be wiped out on a daily basis.

Instead, we can use the ALTER TABLE statement to make changes in place without deleting any data.

With SQLite an ALTER TABLE statement allows you to rename a table or column:

ALTER TABLE employees
RENAME TO contractors;

ALTER TABLE contractors
RENAME COLUMN salary TO invoice;

And add or drop a column:

ALTER TABLE contractors
ADD COLUMN job_title TEXT;

ALTER TABLE contractors
DROP COLUMN is_manager;

Unlike some SQL databases, SQLite does not support performing multiple operations (like adding multiple columns) in a single ALTER TABLE statement. Each change must be made in a separate ALTER TABLE command. SQLite 3.53.0 and later can also add or remove CHECK constraints and set or drop NOT NULL constraints.

Current SQLite versions update references in triggers and views when renaming a table or column. Application queries using the old name will still break, so coordinate schema and application changes.

Adding a column has restrictions. The new column can't use PRIMARY KEY or UNIQUE, and a NOT NULL column needs a non-NULL default unless it meets SQLite's documented generated-column rules. SQLite checks new constraints against existing rows where applicable.

SQLite ALTER TABLE Limitations

SQLite doesn't provide a general ALTER COLUMN command for changing a column's type or every part of its definition. DROP COLUMN also fails when the target column participates in certain primary keys, unique constraints, indexes, foreign keys, generated expressions, triggers, or views.

For unsupported changes, SQLite documents a table reconstruction procedure. A short version is:

1. Create a replacement table with the desired schema.
2. Copy the data into it.
3. Drop the old table.
4. Rename the replacement table.
5. Recreate affected indexes, triggers, and views.

Do this inside a transaction and test it on a copy of the database first. Rebuilding a table incorrectly can lose data or dependent schema objects.

Database Migrations and CREATE TABLE

A database migration records a schema change so each environment applies it in a known order. The initial CREATE TABLE can be a migration, followed by later ALTER TABLE statements.

Keep migrations small and commit them with the application code that depends on them. That creates a reviewable history much like the workflow in Learn Git. Database changes need extra care because production data can't be recreated from source control, as covered in Death, Taxes, and Database Migrations.