CREATE TABLE Reference: MySQL vs PostgreSQL vs SQLite Types & Constraints

The hard part of DDL isn’t the syntax — it’s the dialects

CREATE TABLE is a simple statement. The reason you still end up looking things up every time is that the same column has to be written differently in MySQL, PostgreSQL, and SQLite. “The DDL worked on MySQL but AUTO_INCREMENT is a syntax error on PostgreSQL.” “The CHECK constraint that passed locally on SQLite was never actually enforced in production.” Almost every DDL surprise traces back to a dialect difference.

This is a reference built around cross-database cheat sheets for types and constraints. It’s not about schema design itself (normalization, indexing strategy) — it’s about how to write a design you’ve already decided on.

The shape of a CREATE TABLE

Here’s a minimal definition that runs on all three databases:

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

Each column follows name → type → constraints. Constraints come in two forms: column constraints (written after the type, as above) and table constraints (written after all the column definitions). Composite primary keys and composite unique keys can only be expressed as table constraints.

CREATE TABLE order_items (
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity   INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id),                      -- composite primary key
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
);

Data type cheat sheet

Purpose	MySQL	PostgreSQL	SQLite
Integer (general)	INT	INTEGER	INTEGER
Integer (large)	BIGINT	BIGINT	INTEGER
Money / exact decimals	DECIMAL(10,2)	NUMERIC(10,2)	NUMERIC
Short string	VARCHAR(255)	VARCHAR(255) or TEXT	TEXT
Long text	TEXT	TEXT	TEXT
Boolean	BOOLEAN (really TINYINT(1))	BOOLEAN	INTEGER (0/1)
Date only	DATE	DATE	TEXT
Timestamp	DATETIME	TIMESTAMPTZ	TEXT
JSON	JSON	JSONB	TEXT
UUID	CHAR(36) or BINARY(16)	UUID	TEXT

Three things are worth internalizing.

SQLite barely has types. SQLite is dynamically typed at the value level; a column’s declared type is only a “type affinity” hint. Writing BOOLEAN or DATETIME won’t error, but the value is stored as a number or text underneath. This is the main reason a SQLite target needs its own dialect conversion.

VARCHAR(n) buys you nothing in PostgreSQL. TEXT and VARCHAR share the same implementation there, and the length limit behaves roughly like a CHECK constraint. Unlike MySQL, shorter is not faster — if you don’t have a real business limit, TEXT is fine.

Never store money in FLOAT / DOUBLE. Binary floating point can’t represent decimal fractions exactly, so totals drift. Use DECIMAL / NUMERIC.

Auto-increment keys are three different features

This is the widest dialect gap in everyday DDL.

Database	How to write it
MySQL	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
PostgreSQL (preferred)	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
PostgreSQL (legacy)	id SERIAL PRIMARY KEY
SQLite	id INTEGER PRIMARY KEY

On PostgreSQL, prefer IDENTITY over SERIAL. SERIAL is a pseudo-type meaning “INTEGER plus a sequence created behind the scenes.” Since PostgreSQL 10 you can use the SQL-standard GENERATED ... AS IDENTITY, where the sequence is properly owned by the table — which makes cleanup on table drop and permission handling less surprising.

In SQLite you usually don’t want AUTOINCREMENT. Declaring INTEGER PRIMARY KEY already makes the column an alias for the internal rowid, and it auto-assigns when omitted. Adding the AUTOINCREMENT keyword only adds the guarantee that a deleted ID is never reused, at the cost of extra writes to the sqlite_sequence table. The official documentation recommends avoiding it unless you specifically need that guarantee. Note also that AUTOINCREMENT is a syntax error anywhere other than on INTEGER PRIMARY KEY.

Constraint cheat sheet

Constraint	Meaning	Dialect caveat
PRIMARY KEY	Unique and not null	Implicitly NOT NULL; one per table
NOT NULL	Rejects NULL	Essentially no differences
UNIQUE	Rejects duplicate values	NULLs don’t count as duplicates — many rows can be NULL
DEFAULT	Value used when omitted	MySQL needs parentheses for expression defaults on TEXT/JSON (8.0.13+)
CHECK	Only allows values matching a condition	Parsed but silently ignored before MySQL 8.0.16
FOREIGN KEY	Referential integrity	Disabled by default in SQLite; ignored by non-InnoDB MySQL engines

Two of these cause most of the real incidents.

MySQL’s CHECK constraint. Before MySQL 8.0.16, CHECK clauses were accepted as syntax and then never enforced. That failure mode is quiet: the DDL says the rule is there, and invalid data keeps landing until someone notices months later. If your schema may run on an older server, treat application-level validation as the source of truth.

SQLite’s foreign keys. For backward compatibility, SQLite does not enforce foreign keys by default, and the setting is per connection, so it has to be issued every time you connect:

PRAGMA foreign_keys = ON;

Forget it, and rows referencing a non-existent parent insert happily even though REFERENCES is in the DDL. With SQLite locally and PostgreSQL in production, this shows up as integrity that breaks only on developer machines.

Foreign keys: ON DELETE / ON UPDATE

There are four behaviors when the referenced row is deleted or updated:

Option	Behavior
RESTRICT / NO ACTION	Reject the parent delete/update while children exist (default)
CASCADE	Delete/update the child rows along with the parent
SET NULL	Set the child’s foreign key column to NULL (the column must be nullable)
SET DEFAULT	Set the child to its default value (not supported by MySQL/InnoDB)

-- Deleting a user removes their posts, but keeps comments with an unknown author
CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE comments (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users (id) ON DELETE SET NULL  -- must not be NOT NULL
);

CASCADE is convenient, but the blast radius is invisible unless you read the DDL. Don’t use it for tables you want to survive the parent — audit logs, order line items, anything financial.

Choosing timestamp columns

Goal	MySQL	PostgreSQL
Set created-at automatically	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	TIMESTAMPTZ DEFAULT NOW()
Refresh updated-at automatically	TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Requires a trigger — there’s no column-level equivalent

ON UPDATE CURRENT_TIMESTAMP is MySQL-specific. Migrating to PostgreSQL means replacing it with a BEFORE UPDATE trigger, so a copy-pasted schema leaves updated_at frozen at the insert value without any error to tell you.

When picking a MySQL date type, remember that TIMESTAMP covers only 1970–2038 and is converted using the session time zone, while DATETIME has a wider range and no time zone conversion. On PostgreSQL, TIMESTAMPTZ is the safer default over a naive TIMESTAMP.

In MySQL, the charset is utf8mb4 — not utf8

For historical reasons, MySQL’s utf8 is a different encoding limited to three bytes per character, so emoji and some CJK characters raise Incorrect string value. Specify utf8mb4 explicitly:

CREATE TABLE posts (
    id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    body  TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

PostgreSQL and SQLite handle UTF-8 at the database/file level, so table definitions don’t need to say anything about it.

Turning a design straight into DDL and an ER diagram

Once the types and constraints are decided, writing out three dialects by hand is busywork. DDL Builder lets you assemble tables and columns on screen and generates CREATE TABLE statements for MySQL, PostgreSQL, and SQLite together with a Mermaid ER diagram. You can also paste a JSON sample — an API response, a log line — to infer the columns automatically, which is handy when you’re reverse-engineering a schema from existing data. The dialect differences live in the tool, so you don’t have to re-check this cheat sheet every time.

If you already have DDL, paste it into SQL to ER Diagram to see the relationships as a diagram. Once the tables exist, Visual SQL Builder helps you assemble the SELECT statements. For how the JOIN types differ, see the SQL JOIN types reference; for the diagram syntax itself, see the Mermaid ER diagram reference. All of these run entirely in your browser — the schema you enter is never sent anywhere.

Summary

• For types, remember: SQLite types are only affinities, VARCHAR(n) has no speed benefit in PostgreSQL, and money belongs in DECIMAL
• Auto-increment is three separate features: AUTO_INCREMENT / GENERATED ALWAYS AS IDENTITY / INTEGER PRIMARY KEY. On new PostgreSQL schemas, prefer IDENTITY over SERIAL
• CHECK is ignored before MySQL 8.0.16, and SQLite needs PRAGMA foreign_keys = ON on every connection
• A column used with ON DELETE SET NULL cannot be NOT NULL
• ON UPDATE CURRENT_TIMESTAMP is MySQL-only; PostgreSQL needs a trigger
• In MySQL the charset is utf8mb4, never utf8

FAQ

Should I use VARCHAR or TEXT?

It depends on the database. In PostgreSQL the two are implemented almost identically, so TEXT is fine unless you have a real length limit to enforce. In MySQL, VARCHAR is stored inline while TEXT may be stored off-page, so short strings you filter and sort on often are better as VARCHAR(n). In SQLite both declarations behave the same internally.

Should I add AUTOINCREMENT in SQLite?

Usually not. INTEGER PRIMARY KEY already auto-assigns when the value is omitted. AUTOINCREMENT only adds a guarantee that deleted IDs are never reused, and it costs extra writes to a bookkeeping table. Reach for it only when reusing a previously issued ID would be a real problem — for example, IDs exposed to external systems.

My CHECK constraint isn’t being enforced

If you’re on MySQL, check whether the server predates 8.0.16 — older versions accept the CHECK syntax without applying it. Run SELECT VERSION(); to confirm. On SQLite, CHECK does work, but foreign keys are the ones disabled by default, so you need PRAGMA foreign_keys = ON; on every connection.

Is the DDL I paste in sent to a server?

No. Both DDL Builder and SQL to ER Diagram run entirely in your browser — the table definitions and schema details you enter are never transmitted anywhere.