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.

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

A simple example — a table to store product categories:

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.

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.

-- 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.

-- 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.

-- 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
);

Full Real-World Example

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

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:

-- 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

CREATE TABLE: Getting the Schema Right the First Time

CREATE TABLE defines columns, their types, and the constraints that guard your data. Decisions here are expensive to change later, so it pays to be deliberate.

CREATE TABLE IF NOT EXISTS orders (
  id          SERIAL      PRIMARY KEY,          -- auto-incrementing identity
  customer_id INT         NOT NULL REFERENCES customers(id),
  total       NUMERIC(10,2) NOT NULL CHECK (total >= 0),
  status      TEXT        NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Design habits: give every table a primary key (queries, joins, and updates all rely on it). Push data rules into constraints at creation time — a CHECK (total >= 0) or NOT NULL guarantees valid data no matter what code writes to it, far safer than hoping the app validates. Add DEFAULTs so partial inserts still produce sensible rows. Set foreign keys to make relationships explicit and prevent orphans.

This is DDL (Data Definition Language) — it changes structure, not rows. In production, apply schema changes through versioned migration files, not ad-hoc CREATE/ALTER statements, so every environment stays in sync.

Interview Questions

• What is the syntax of CREATE TABLE?
• How do you define a primary key?
• What does NOT NULL enforce?
• How do you set a default value?
• What is auto-increment / serial?

Related Topics