🏗️ SchemabeginnerCommonly used

How to create a table in SQL

Define a new table with columns, data types and constraints.

Basic CREATE TABLEPostgreSQL

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  name       TEXT          NOT NULL,
  email      TEXT          UNIQUE NOT NULL,
  age        INTEGER,
  country    TEXT          DEFAULT 'Unknown',
  active     BOOLEAN       DEFAULT true,
  created_at TIMESTAMP     DEFAULT NOW()
);

Creates a table with common data types and constraints. SERIAL auto-increments the id.

CREATE TABLE (MySQL)MySQL

CREATE TABLE users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(255)  NOT NULL,
  email      VARCHAR(255)  UNIQUE NOT NULL,
  age        INT,
  country    VARCHAR(100)  DEFAULT 'Unknown',
  active     TINYINT(1)    DEFAULT 1,
  created_at DATETIME      DEFAULT CURRENT_TIMESTAMP
);

MySQL uses AUTO_INCREMENT and VARCHAR instead of SERIAL and TEXT.

CREATE TABLE with foreign keyPostgreSQL

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER       NOT NULL,
  total      DECIMAL(10,2) NOT NULL,
  status     TEXT          DEFAULT 'pending',
  created_at TIMESTAMP     DEFAULT NOW(),

  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
);

The FOREIGN KEY constraint links orders.user_id to users.id. ON DELETE CASCADE deletes orders when the user is deleted.

CREATE TABLE IF NOT EXISTSAll databases

CREATE TABLE IF NOT EXISTS users (
  id    SERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

Silently skips creation if the table already exists. Safe for migrations.

Common Mistakes

→Not adding NOT NULL to required columns — NULLs can sneak into data
→Using TEXT vs VARCHAR — in PostgreSQL TEXT is fine, MySQL requires VARCHAR with a length

Common Mistakes

Related Topics