DDL Differences: MySQL, PostgreSQL, Oracle, SQL Server, and SQLite

At a Glance

The table below summarises the most important DDL differences. Each section below drills into one topic with side-by-side code examples.

Primary Keys and Auto-Increment

Auto-incrementing integer primary keys are the most visible DDL difference between databases. Every database has its own keyword or mechanism, and the underlying behaviour varies in subtle ways.

CREATE TABLE users (
  id INT UNSIGNED NOT NULL
     AUTO_INCREMENT,
  PRIMARY KEY (id)
);

-- modern standard (PG 10+)
CREATE TABLE users (
  id INT GENERATED ALWAYS
     AS IDENTITY PRIMARY KEY
);

-- legacy shorthand
-- id SERIAL PRIMARY KEY

-- Oracle 12c+
CREATE TABLE users (
  id NUMBER GENERATED ALWAYS
     AS IDENTITY PRIMARY KEY
);

-- Oracle 11g and older:
-- use a SEQUENCE + trigger

CREATE TABLE users (
  id INT NOT NULL
     IDENTITY(1, 1),
  CONSTRAINT PK_users
     PRIMARY KEY (id)
);

CREATE TABLE users (
  id INTEGER PRIMARY KEY
  -- AUTOINCREMENT keyword
  -- is optional and changes
  -- the reuse behaviour
);

Key behavioural notes:

• MySQL — AUTO_INCREMENT reuses gaps in SQLite style unless you use NO_AUTO_VALUE_ON_ZERO. The counter resets on server restart if the table is empty (before MySQL 8.0).
• PostgreSQL — SERIAL creates a backing sequence object. GENERATED ALWAYS AS IDENTITY is the SQL:2003 standard equivalent and is preferred for new schemas.
• Oracle — Before 12c, sequences were always separate objects and had to be fed into the column via a trigger or called explicitly in the INSERT.
• SQL Server — IDENTITY(seed, increment). Once a row with an identity value is inserted, you cannot insert an explicit value without SET IDENTITY_INSERT table ON.
• SQLite — INTEGER PRIMARY KEY (without the AUTOINCREMENT keyword) is an alias for the internal rowid and reuses deleted values. Adding AUTOINCREMENT prevents reuse but has a small performance cost.

String Types

String storage is one of the most divergent areas across databases, particularly around Unicode support and maximum lengths.

Important differences to be aware of:

• Oracle uses VARCHAR2, not VARCHAR — Oracle's VARCHAR is reserved and may behave differently in future versions. Always use VARCHAR2.
• SQL Server stores Unicode by default with N prefix types — NVARCHAR and NCHAR store Unicode (UTF-16). Plain VARCHAR is ASCII only. For any modern application use NVARCHAR.
• PostgreSQL's TEXT is unlimited — TEXT and VARCHAR(n) have the same performance; the length limit is just a constraint, not a storage optimisation.
• SQLite ignores type affinity at storage level — SQLite stores any string as TEXT regardless of whether you declare the column as VARCHAR(255) or CHAR(10). Type names are only advisory.

Numeric Types

Integer and decimal types are broadly consistent in naming, but there are gaps:

Notable differences:

• Oracle has no separate integer types — everything is NUMBER(p,s). INTEGER is an alias for NUMBER(38) and lacks the compact storage of a true 4-byte integer.
• SQLite uses dynamic typing — any integer you declare gets stored in 1–8 bytes depending on value, regardless of the declared type name.
• MySQL supports UNSIGNED — INT UNSIGNED doubles the positive range (0 to ~4.3B). No other major database supports this modifier.
• Use DECIMAL or NUMERIC for monetary values in all databases. Never use FLOAT or DOUBLE for money.

Boolean Type

Boolean is one of the most inconsistent types across SQL databases:

PostgreSQL is the only database here with a true native boolean type. Oracle has no boolean type at all in SQL (PL/SQL has one, but it cannot be used as a column type). SQL Server's BIT accepts 1/0 and 'true'/'false' strings but is not a proper boolean.

Date and Time Types

Critical Oracle gotcha: Oracle's DATE type stores both date and time (to the nearest second). This is unlike every other database where DATE is date-only. If you query WHERE event_date = DATE '2026-05-01' in Oracle and the column has a time component, you'll get no results. Use TRUNC(event_date) to strip the time, or use TIMESTAMP columns from the start.

SQL Server has deprecated the older DATETIME type in favour of DATETIME2, which has higher precision (100ns vs 3ms) and a wider range. Use DATETIME2 in new SQL Server schemas.

CHECK Constraints

CHECK constraints let you enforce rules at the database level — for example, that a status column can only hold certain values, or that a price must be positive.

-- Works the same way in MySQL 8.0.16+, PostgreSQL, Oracle, and SQL Server
CREATE TABLE orders (
    id          INT PRIMARY KEY,
    status      VARCHAR(20) NOT NULL,
    total_cents INT NOT NULL,
    CONSTRAINT chk_status  CHECK (status IN ('pending', 'paid', 'cancelled')),
    CONSTRAINT chk_total   CHECK (total_cents >= 0)
);

The SQL above is portable across all five databases with minor syntax adjustments. The key differences are enforcement history:

• MySQL — CHECK constraints were parsed but silently ignored before MySQL 8.0.16. Any existing schema built on MySQL 5.7 or earlier that uses CHECK for data integrity may have dirty data in it.
• PostgreSQL, Oracle, SQL Server — always enforced CHECK constraints fully.
• SQLite — enforced CHECK constraints from version 3.25.0 (2018). Very old SQLite builds may ignore them.

DEFAULT Values

Specifying default values for columns is mostly consistent, but the function names for current timestamp differ:

-- MySQL
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- PostgreSQL
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()  -- triggers or rules needed for auto-update

-- Oracle
created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL

-- SQL Server
created_at DATETIME2 NOT NULL DEFAULT GETDATE(),
updated_at DATETIME2 NOT NULL DEFAULT GETDATE()

-- SQLite
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP

MySQL's ON UPDATE CURRENT_TIMESTAMP is a MySQL-only extension that automatically refreshes the column on any UPDATE. PostgreSQL, Oracle, and SQL Server require a trigger to replicate this behaviour. SQLite has no trigger-free equivalent.

Generated (Computed) Columns

Generated columns derive their value from an expression and are recalculated automatically. They are useful for storing pre-computed values like full names or totals without duplicating logic in application code.

ALTER TABLE products ADD COLUMN
  total_price DECIMAL(10,2)
  GENERATED ALWAYS AS
    (quantity * unit_price)
  STORED;

ALTER TABLE products ADD COLUMN
  total_price DECIMAL(10,2)
  GENERATED ALWAYS AS
    (quantity * unit_price)
  STORED;
-- VIRTUAL not yet supported

ALTER TABLE products ADD (
  total_price NUMBER
  GENERATED ALWAYS AS
    (quantity * unit_price)
  VIRTUAL
);

ALTER TABLE products ADD
  total_price AS
    (quantity * unit_price)
  PERSISTED;

-- Must be in CREATE TABLE
total_price REAL
  GENERATED ALWAYS AS
    (quantity * unit_price)
  STORED;

STORED vs VIRTUAL — a stored (persisted) generated column writes the computed value to disk, making reads fast but writes slightly slower. A virtual column recomputes on every read. PostgreSQL only supports STORED. Oracle defaults to VIRTUAL. MySQL supports both.

ALTER TABLE Differences

Modifying an existing table is where dialects diverge most sharply. Here are the most common operations:

Add a column

-- MySQL, PostgreSQL, SQLite
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Oracle
ALTER TABLE users ADD (phone VARCHAR2(20));

-- SQL Server
ALTER TABLE users ADD phone NVARCHAR(20);

Rename a column

-- MySQL 8+, PostgreSQL
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- Oracle 12c R2+
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- SQL Server (uses a system stored procedure, not standard SQL)
EXEC sp_rename 'users.phone', 'phone_number', 'COLUMN';

-- SQLite — not supported directly.
-- You must recreate the table.

Change a column's data type

-- MySQL
ALTER TABLE users MODIFY COLUMN age SMALLINT;

-- PostgreSQL
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- Oracle
ALTER TABLE users MODIFY (age NUMBER(5));

-- SQL Server
ALTER TABLE users ALTER COLUMN age SMALLINT;

-- SQLite — not supported. Recreate the table.

Drop a column

-- MySQL, PostgreSQL, Oracle, SQL Server
ALTER TABLE users DROP COLUMN phone;

-- SQLite — not supported before version 3.35.0 (2021).
-- Upgrade or recreate the table.

Summary: Which Differences Matter Most in Practice

If you're designing a schema from scratch, here's where to focus your attention:

• Primary keys — the biggest immediate syntax difference. Know your database's keyword before writing your first CREATE TABLE.
• String types — use VARCHAR2 on Oracle, NVARCHAR on SQL Server, and VARCHAR everywhere else. SQLite accepts anything and stores it as TEXT.
• Booleans — only PostgreSQL has a true native BOOLEAN. Use TINYINT(1), BIT, or NUMBER(1) with a CHECK constraint elsewhere.
• Oracle's DATE includes time — this surprises almost everyone coming from MySQL or PostgreSQL. Use TIMESTAMP in Oracle if you only want a date.
• CHECK enforcement history — if you're working with a MySQL 5.7 or earlier schema, CHECK constraints were ignored. Verify data integrity before migrating.
• SQLite ALTER TABLE limitations — SQLite is not suitable for schemas that need frequent structural changes in production. Column drops and renames require a recent SQLite version.
• Timestamp defaults — CURRENT_TIMESTAMP works in MySQL and SQLite; PostgreSQL prefers NOW(); Oracle uses SYSTIMESTAMP; SQL Server uses GETDATE() or SYSDATETIME().

Whichever database you're targeting, modelling your schema visually before writing DDL makes it easier to catch type mismatches and missing constraints early. SQL Designer supports both MySQL and PostgreSQL dialects and exports ready-to-run CREATE TABLE scripts — try the demo with your own schema.