SQL CREATE TABLE Syntax and Examples

CREATE TABLE Fundamentals

The CREATE TABLE statement defines a new table in your database. It is the cornerstone of SQL Data Definition Language (DDL) and the first command most developers learn.

Basic Syntax

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

Column Constraints

Constraint	Purpose
PRIMARY KEY	Uniquely identifies each row
NOT NULL	Prevents null values
UNIQUE	Ensures all values in the column are distinct
DEFAULT value	Sets a fallback value when none is provided
CHECK (condition)	Validates data against a condition
REFERENCES table(col)	Creates a foreign key relationship

Dialect Differences

• PostgreSQL uses SERIAL or GENERATED ALWAYS AS IDENTITY for auto-incrementing integers, and supports TIMESTAMPTZ for timezone-aware timestamps.
• MySQL uses AUTO_INCREMENT and requires an ENGINE specification (typically InnoDB).
• SQLite uses INTEGER PRIMARY KEY AUTOINCREMENT and stores dates as TEXT since it lacks a native date type.

Practical Example

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  category_id INT REFERENCES categories(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Understanding CREATE TABLE thoroughly is essential because every other SQL operation depends on the schema you define here.