10.2. CREATE

Table of Contents

Why `CREATE` Matters in SQL

In SQL, the CREATE family of commands is how you define things in your database: databases, tables, indexes, views, and more. In this chapter, you will focus on the most important use case for backend development, creating tables.

Other chapters in this section will show you how to insert, query, update, and delete data. Here you will learn how to define the structure that all those operations will use.

Key idea:
CREATE commands define structure, not data.
Once a structure exists, you use other commands such as INSERT, SELECT, UPDATE, and DELETE to work with the data inside it.

Throughout the chapter, examples will use a PostgreSQL like syntax, which is very common in backend work.

The Basic `CREATE TABLE` Syntax

The most important command you will use is CREATE TABLE. It defines a new table and its columns.

A simplified version of the syntax is:

sql

CREATE TABLE table_name (
    column_name1 data_type1 constraint1,
    column_name2 data_type2 constraint2,
    ...
);

Some points to notice:

CREATE TABLE is the command.
table_name is the name you choose for the table.
Inside the parentheses you list columns, one per line.
Each column has:

a name
a data type
optional constraints such as NOT NULL, UNIQUE, PRIMARY KEY

Rule:
Every column must have a data type. You cannot define a column without a type.

Creating Your First Table

Imagine a simple backend for a task management app. You might need a users table to store user accounts.

sql

CREATE TABLE users (
    id          SERIAL       PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    full_name   VARCHAR(100) NOT NULL,
    is_active   BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Line by line:

id SERIAL PRIMARY KEY

SERIAL in PostgreSQL is an auto-incrementing integer
PRIMARY KEY marks this as the unique identifier for each row

email VARCHAR(255) NOT NULL UNIQUE

VARCHAR(255) stores up to 255 characters
NOT NULL means every user must have an email
UNIQUE means no two users can share the same email

full_name VARCHAR(100) NOT NULL

must always be provided

is_active BOOLEAN NOT NULL DEFAULT TRUE

BOOLEAN is true or false
DEFAULT TRUE gives new rows a default value

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

stores date and time when the row was created

You will learn about primary keys, unique constraints, and data types in more detail in other chapters. For now, focus on recognizing the pattern of column name, type, constraints.

Common Data Types in `CREATE TABLE`

When you create a table you must pick a type for each column. Here are common types you will see often.

Category	Data type examples	Typical use
Integers	`INT`, `INTEGER`, `BIGINT`	IDs, counters, numeric flags
Auto ID	`SERIAL`, `BIGSERIAL`	Auto incrementing primary keys (PostgreSQL)
Text	`VARCHAR(n)`, `TEXT`	Names, emails, descriptions
Boolean	`BOOLEAN`	True or false values
Date/Time	`DATE`, `TIME`, `TIMESTAMP`	Birthdays, created_at, updated_at
Numeric	`NUMERIC(p, s)`, `DECIMAL`	Money, prices, precise decimals

Examples of columns with different types:

sql

CREATE TABLE products (
    id          SERIAL        PRIMARY KEY,
    name        VARCHAR(200)  NOT NULL,
    description TEXT,
    price       NUMERIC(10, 2) NOT NULL,
    in_stock    BOOLEAN       NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

NUMERIC(10, 2) means up to 10 digits in total, with 2 digits after the decimal point, such as 12345678.90.

Important guideline:
For money values, use an exact type like NUMERIC or DECIMAL, not a floating point type. This avoids rounding errors.

`NULL`, `NOT NULL`, and Defaults

In CREATE TABLE definitions, you almost always specify whether a column can be NULL or not.

NULL means “no value” or “unknown”
NOT NULL means the value is required

Example:

sql

CREATE TABLE blog_posts (
    id           SERIAL        PRIMARY KEY,
    title        VARCHAR(200)  NOT NULL,
    content      TEXT          NOT NULL,
    published_at TIMESTAMP     NULL,
    is_published BOOLEAN       NOT NULL DEFAULT FALSE
);

Here:

title and content must always be provided, so they are NOT NULL.
published_at can be NULL. For a draft blog post you might not know the publish time yet.
is_published defaults to FALSE.

You can use DEFAULT to give a value when none is provided.

sql

CREATE TABLE customers (
    id             SERIAL         PRIMARY KEY,
    email          VARCHAR(255)   NOT NULL UNIQUE,
    country        VARCHAR(2)     NOT NULL DEFAULT 'US',
    signup_source  VARCHAR(50)    NOT NULL DEFAULT 'web',
    created_at     TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
);

If you insert a row with only email, the other columns take their default values.

Creating Tables With Primary Keys

A primary key uniquely identifies each row. You will learn more in the “Primary Keys” chapter, but you will already use the syntax here.

There are two main ways to define a primary key.

1. Primary key on the same line as the column

sql

CREATE TABLE categories (
    id   SERIAL       PRIMARY KEY,
    name VARCHAR(50)  NOT NULL UNIQUE
);

Here id is both a column and the primary key.

2. Primary key as a separate table constraint

sql

CREATE TABLE categories (
    id   SERIAL,
    name VARCHAR(50) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

Both versions do the same thing. The second style becomes more useful if you have a composite primary key (a primary key made of several columns), for example:

sql

CREATE TABLE user_roles (
    user_id  INT  NOT NULL,
    role_id  INT  NOT NULL,
    PRIMARY KEY (user_id, role_id)
);

This says: the combination of (user_id, role_id) must be unique, and together they identify a row.

Creating Multiple Related Tables

Often you will create several tables that relate to each other. Relationships are covered later, but it is helpful to see how you might define them in CREATE TABLE statements.

For example, a simple blogging system:

sql

CREATE TABLE authors (
    id         SERIAL        PRIMARY KEY,
    email      VARCHAR(255)  NOT NULL UNIQUE,
    full_name  VARCHAR(100)  NOT NULL,
    joined_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE posts (
    id          SERIAL        PRIMARY KEY,
    author_id   INT           NOT NULL,
    title       VARCHAR(200)  NOT NULL,
    content     TEXT          NOT NULL,
    published   BOOLEAN       NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Here:

authors is created first.
posts refers to author_id, which is intended to match authors.id.

Later, when you learn about foreign keys, you will see how to enforce this relationship at the database level by adding a FOREIGN KEY constraint inside CREATE TABLE.

`IF NOT EXISTS` to Avoid Errors

If you run a CREATE TABLE statement for a table that already exists, you usually get an error. To avoid this, many SQL dialects allow IF NOT EXISTS.

sql

CREATE TABLE IF NOT EXISTS users (
    id         SERIAL        PRIMARY KEY,
    email      VARCHAR(255)  NOT NULL UNIQUE,
    full_name  VARCHAR(100)  NOT NULL
);

If users already exists, this statement does nothing and does not fail.

Tip for backend apps:
When you run migrations or initialization scripts multiple times, prefer CREATE TABLE IF NOT EXISTS in raw SQL to make scripts idempotent. In real projects you will often use a migration tool that manages this for you.

Creating Indexes (Preview)

You will have a separate chapter on indexes, but in practice indexes are often created with CREATE.

Example:

sql

CREATE INDEX idx_users_email ON users (email);

CREATE INDEX defines an index.
idx_users_email is the index name.
ON users (email) specifies which table and which column.

For unique indexes:

sql

CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

Often, you use a UNIQUE constraint inside CREATE TABLE instead, but it is useful to see that CREATE is used for these structures as well.

Other `CREATE` Commands You Will Encounter

Beyond tables and indexes, you will commonly see:

CREATE DATABASE db_name;
CREATE SCHEMA schema_name;
CREATE VIEW view_name AS SELECT ...;
CREATE EXTENSION ...; (PostgreSQL specific)

Examples:

sql

CREATE DATABASE my_app;

sql

CREATE VIEW active_users AS
SELECT id, email
FROM users
WHERE is_active = TRUE;

You are not expected to master these in this chapter, but recognize that CREATE is the verb SQL uses to define new database objects.

Putting It All Together: Small Schema Example

Imagine a minimal backend for a todo application. You might define this schema:

sql

CREATE TABLE IF NOT EXISTS users (
    id         SERIAL        PRIMARY KEY,
    email      VARCHAR(255)  NOT NULL UNIQUE,
    full_name  VARCHAR(100)  NOT NULL,
    created_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS todos (
    id          SERIAL        PRIMARY KEY,
    user_id     INT           NOT NULL,
    title       VARCHAR(200)  NOT NULL,
    description TEXT,
    is_done     BOOLEAN       NOT NULL DEFAULT FALSE,
    due_date    DATE,
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_todos_user_id ON todos (user_id);
CREATE INDEX IF NOT EXISTS idx_todos_is_done ON todos (is_done);

This gives you:

A users table to store users.
A todos table to store tasks that belong to users.
Indexes on user_id and is_done to speed up common queries.

Later, you will add foreign keys, constraints, and migrations, but this is already a realistic starting point that you can use with INSERT, SELECT, and other commands from upcoming chapters.

Summary

CREATE is used to define database structures, especially tables.
CREATE TABLE lists columns, each with a name, data type, and optional constraints.
Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, DEFAULT.
Primary keys identify rows. You can define them in line with a column or separately.
IF NOT EXISTS helps avoid errors when the object already exists.
CREATE is also used for databases, indexes, views, and more.

Once your tables exist, the remaining SQL commands in this section will show you how to work with the data inside them.