CREATE TABLE – Defining Tables in SQL

Basic CREATE TABLE Syntax

The minimal CREATE TABLE statement lists the table name followed by column definitions inside parentheses. Each column definition specifies the column name and its data type.

SQL

CREATE TABLE table_name (
  column1  data_type  [constraints],
  column2  data_type  [constraints],
  ...
  [table_constraints]
);

A simple example — a table to store product categories:

SQL

CREATE TABLE categories (
  id    INT          PRIMARY KEY,
  name  VARCHAR(100) NOT NULL
);

Column Definitions

Each column definition can include: the column name, its data type, and optional inline constraints such as NOT NULL, UNIQUE, and DEFAULT.

SQL

CREATE TABLE products (
  id          INT            NOT NULL,         -- cannot be NULL
  name        VARCHAR(200)   NOT NULL,
  price       DECIMAL(10,2)  NOT NULL,
  description TEXT,                            -- allows NULL by default
  stock       INT            DEFAULT 0,        -- default value
  sku         VARCHAR(50)    UNIQUE,           -- must be unique across rows
  created_at  TIMESTAMP      DEFAULT CURRENT_TIMESTAMP
);

Defining a PRIMARY KEY

A primary key uniquely identifies each row. You can declare it inline (on the column) or at the table level for composite (multi-column) primary keys.

SQL

-- Inline primary key (single column)
CREATE TABLE users (
  id    INT  PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

-- Table-level primary key (required for composite PKs)
CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL DEFAULT 1,
  PRIMARY KEY (order_id, product_id)   -- composite primary key
);

AUTO_INCREMENT / SERIAL / AUTOINCREMENT

In practice, primary key IDs are generated automatically by the database rather than supplied by the application. Each database engine has its own syntax for this.

Database	Auto-increment syntax
MySQL / MariaDB	`INT PRIMARY KEY AUTO_INCREMENT`
PostgreSQL (legacy)	`SERIAL` (shorthand for `INT` + sequence)
PostgreSQL (modern)	`INT GENERATED ALWAYS AS IDENTITY`
SQLite	`INTEGER PRIMARY KEY` (rowid alias — always auto-increments)
SQL Server	`INT IDENTITY(1,1)`

SQL

-- MySQL / MariaDB
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL
);

-- PostgreSQL (modern, preferred)
CREATE TABLE users (
  id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

-- PostgreSQL (legacy shorthand)
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

-- SQLite
CREATE TABLE users (
  id    INTEGER PRIMARY KEY,   -- INTEGER (not INT) triggers rowid alias
  email TEXT NOT NULL
);

IF NOT EXISTS

Running CREATE TABLE on a table that already exists throws an error. The IF NOT EXISTS clause makes the statement a no-op if the table is already present — useful in scripts and migrations.

SQL

-- Without IF NOT EXISTS — errors if table already exists:
CREATE TABLE sessions ( ... );  -- ERROR if sessions exists

-- With IF NOT EXISTS — safe to run multiple times:
CREATE TABLE IF NOT EXISTS sessions (
  id         CHAR(36)  PRIMARY KEY,  -- UUID
  user_id    INT       NOT NULL,
  expires_at TIMESTAMP NOT NULL
);

💡

Always use IF NOT EXISTS in migration scripts

Migration scripts are often re-run (e.g., during testing or deployment retries). Including IF NOT EXISTS prevents the script from crashing if a table was already created in a previous run. It is a best practice in all database migration workflows.

Full Real-World Example

Here is a complete users table that combines everything covered in this lesson:

SQL

CREATE TABLE IF NOT EXISTS users (
  id          INT           PRIMARY KEY AUTO_INCREMENT,
  email       VARCHAR(255)  NOT NULL UNIQUE,
  first_name  VARCHAR(100)  NOT NULL,
  last_name   VARCHAR(100)  NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role        VARCHAR(50)   NOT NULL DEFAULT 'user',
  is_active   TINYINT(1)    NOT NULL DEFAULT 1,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
                              ON UPDATE CURRENT_TIMESTAMP
);

Key points in this example:

AUTO_INCREMENT means the database assigns the id automatically — you never insert it manually.
email is both NOT NULL (required) and UNIQUE (no duplicates).
role has a default value of 'user', so it is optional in INSERT statements.
updated_at uses ON UPDATE CURRENT_TIMESTAMP (MySQL) to automatically update when the row changes.

Viewing Your Tables

After creating a table, you can inspect it with these commands:

SQL

-- MySQL: list all tables in the current database
SHOW TABLES;

-- MySQL: see the CREATE TABLE statement for a table
SHOW CREATE TABLE users;

-- MySQL: describe columns
DESCRIBE users;
-- or:
DESC users;

-- PostgreSQL: list tables in current schema
\dt

-- PostgreSQL: describe a table
\d users

-- SQLite: list tables
.tables

-- SQLite: show create statement
.schema users

-- Standard SQL (works in most databases)
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_database_name';

Summary

📋 Summary

CREATE TABLE defines a new table with column names, data types, and constraints.
Each column can have inline constraints: NOT NULL, UNIQUE, DEFAULT, PRIMARY KEY.
Use AUTO_INCREMENT (MySQL), SERIAL or GENERATED ALWAYS AS IDENTITY (PostgreSQL), or INTEGER PRIMARY KEY (SQLite) for auto-generated IDs.
IF NOT EXISTS prevents errors when re-running scripts and is essential in migration files.
Composite primary keys are defined at the table level: PRIMARY KEY (col1, col2).

FAQ

What happens if I try to CREATE TABLE with a name that already exists? +

Without IF NOT EXISTS, the database returns an error and the statement is aborted. The existing table is not modified. With IF NOT EXISTS, the database silently does nothing — no error, no change to the existing table. Note that IF NOT EXISTS will not tell you if the schema of the existing table matches what you defined; it just checks whether the table name exists.

Can I create a table from the result of a SELECT query? +

Yes — this is called CREATE TABLE AS SELECT (CTAS). Example: CREATE TABLE active_users AS SELECT * FROM users WHERE is_active = 1;. The new table inherits the columns and data from the query result, but not the indexes, constraints, or auto-increment from the source table. It is useful for creating summary tables, snapshots, or temporary analysis tables.

What is the difference between SERIAL and GENERATED ALWAYS AS IDENTITY in PostgreSQL? +

SERIAL is a PostgreSQL shorthand that creates an integer column and an attached sequence — it is not SQL standard. GENERATED ALWAYS AS IDENTITY is the SQL:2003-standard way and is preferred in modern PostgreSQL (v10+). The key practical difference: with GENERATED ALWAYS, you cannot manually insert a value into that column without using OVERRIDING SYSTEM VALUE, making it stricter. Use GENERATED BY DEFAULT AS IDENTITY if you sometimes need to supply values (e.g., during data migrations).

Should every table have a primary key? +

Yes — almost always. A primary key guarantees that every row is uniquely identifiable, which is essential for JOINs, foreign key references, updates, and deletes. Without a primary key, you can end up with duplicate rows you cannot distinguish from each other. The main exceptions are pure staging/temporary tables where you are loading raw data before cleaning. In MySQL's InnoDB engine, the primary key also acts as the clustered index, so it has a direct impact on query performance.