SQL CREATE TABLE Generator

SQL CREATE TABLE in depth: dialects, constraints, and schema design

The CREATE TABLE statement is the cornerstone of DDL (Data Definition Language) — the subset of SQL that defines the shape of your data instead of querying or mutating rows. Every relational database from SQLite on a phone to Aurora running thousands of transactions per second starts with the same handful of declarative lines describing columns, types, defaults, and constraints. Getting the schema right at creation time is one of the highest-leverage decisions in a project's lifetime: types, primary keys, and foreign keys are notoriously expensive to change once a table holds millions of rows.

This generator emits portable starter DDL; below is a deeper reference covering the dialect differences (PostgreSQL, MySQL, SQLite, SQL Server), constraint families, generated columns, partitioning, and migration tooling that every backend engineer eventually needs.

Basic syntax

The minimal form lists a table name and a parenthesized column list. Each column declaration is name type [constraints]:

CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Add IF NOT EXISTS for idempotent migrations and CREATE TEMPORARY TABLE for session-scoped tables used inside ETL or reports.

Types by dialect

• PostgreSQL: SERIAL/BIGSERIAL or modern GENERATED ALWAYS AS IDENTITY, JSONB (indexable JSON), UUID, TIMESTAMPTZ (timezone-aware), NUMERIC(p,s), native ARRAY types and ENUM via CREATE TYPE.
• MySQL / MariaDB: INT AUTO_INCREMENT, JSON (since 5.7), inline ENUM('a','b'), DATETIME vs TIMESTAMP (the latter is 4 bytes, UTC-stored, but limited to 2038), DECIMAL(p,s), and storage-engine-specific quirks (InnoDB vs MyISAM).
• SQLite: only five storage classes (INTEGER, TEXT, REAL, BLOB, NUMERIC) plus type affinity. The magic incantation INTEGER PRIMARY KEY aliases the internal ROWID and is the fastest possible key.
• SQL Server: INT IDENTITY(1,1), NVARCHAR(n) for Unicode, DATETIME2 (preferred over the older DATETIME), UNIQUEIDENTIFIER, and computed columns via AS.

Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, DEFAULT

Constraints are how the database enforces invariants that application code can forget. Every table should have a single PRIMARY KEY (implicitly NOT NULL UNIQUE); composite keys are legal but harder to reference from child tables. FOREIGN KEY declarations link to a parent row and accept referential actions:

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id)
             ON DELETE CASCADE ON UPDATE NO ACTION,
  total      NUMERIC(10,2) NOT NULL CHECK (total >= 0),
  status     VARCHAR(20) NOT NULL DEFAULT 'pending',
  CONSTRAINT chk_status CHECK (status IN ('pending','paid','shipped'))
);

ON DELETE options: CASCADE deletes children automatically, SET NULL nulls the FK, SET DEFAULT uses the column default, RESTRICT blocks the parent delete immediately, and NO ACTION defers the check to the end of the transaction.

Generated columns, indexes, and partitioning

Generated (computed) columns derive their value from an expression and avoid duplication between insert paths:

qty   INTEGER NOT NULL,
price NUMERIC(10,2) NOT NULL,
total NUMERIC(12,2) GENERATED ALWAYS AS (qty * price) STORED

Some dialects (MySQL, SQL Server) allow inline UNIQUE INDEX (col) inside CREATE TABLE; most projects keep indexes in separate CREATE INDEX statements so they can be added concurrently in production without rewriting the table. Partitioning splits a logical table into physical pieces by range, list, or hash — declarative PARTITION BY RANGE (created_at) has been available in PostgreSQL since 10 and in MySQL since 5.1, and dramatically improves prune-time on time-series workloads.

Naming and schema-design best practices

• Use snake_case plural table names (users, order_items) — they read naturally next to foreign keys (user_id).
• Pick one PK strategy and stick to it: surrogate BIGINT IDENTITY for OLTP, UUID v7 when IDs leak to clients or are generated offline.
• Add created_at + updated_at timestamps everywhere; the cost is two columns and the value at debug time is enormous.
• Consider soft delete (deleted_at TIMESTAMP NULL) for user-facing entities so accidental removals can be reversed.
• Audit columns (created_by_id, updated_by_id) make compliance and forensics trivial.

Migration tooling

In production you rarely run CREATE TABLE by hand; instead, a migration framework versions each change and applies it deterministically across environments. Popular choices: Flyway and Liquibase (JVM), Alembic (Python/SQLAlchemy), Rails migrations (Ruby), Knex.js and Prisma Migrate (Node.js), golang-migrate (Go), and EF Core Migrations (.NET). All record applied versions in a metadata table and support rollback in some form.

FAQ

Can a table have zero columns? No — the SQL standard requires at least one column. PostgreSQL technically allows a table with only generated metadata columns, but it is a curiosity, not a pattern.

How do I change the primary key after creation? Use ALTER TABLE ... DROP CONSTRAINT followed by ADD CONSTRAINT. Some engines require recreating the table; tools like pt-online-schema-change (MySQL) or pg_repack (PostgreSQL) do it without long locks.

Are types portable across dialects? Not really. VARCHAR, INT, and TIMESTAMP are mostly compatible, but anything richer (JSONB, UUID, ENUM, arrays, identity syntax) diverges. Use an ORM or a SQL abstraction layer if you need true portability.

Should I use VARCHAR(255) by default? No. Pick a size that reflects the domain (emails 255, names 100, slugs 80). On PostgreSQL TEXT has no length limit and the same performance as VARCHAR.

Is CREATE TABLE transactional? In PostgreSQL and SQL Server, yes — wrap it in BEGIN ... COMMIT with related ALTERs. In MySQL, DDL implicitly commits, so plan rollbacks differently (atomic DDL since 8.0 helps).