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

At a Glance

Five major SQL databases share the same core DDL concepts but use incompatible syntax. MySQL relies on AUTO_INCREMENT while SQL Server uses IDENTITY(1,1). Oracle stores all integers as NUMBER(p,s) while PostgreSQL has dedicated BIGINT and SMALLINT types. SQLite ignores declared type names entirely at the storage level. The table below summarizes the most important differences; each section below drills into one topic with side-by-side code examples.

How Does Each Database Handle Auto-Increment Primary Keys?

Auto-incrementing integer primary keys are the most visible DDL difference between databases. Every database uses its own keyword or mechanism, and the underlying behaviour varies in subtle ways. MySQL's AUTO_INCREMENT is a column-level attribute. PostgreSQL offers SERIAL (a shorthand that creates a backing sequence) and GENERATED ALWAYS AS IDENTITY (the SQL:2003 standard form, preferred for new schemas from PostgreSQL 10 onwards). SQL Server uses IDENTITY(seed, increment). Oracle added native identity columns in 12c; older versions require a separate sequence object plus a trigger. SQLite takes the simplest approach: declare INTEGER PRIMARY KEY and the column automatically aliases the internal rowid.

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 (MySQL docs): AUTO_INCREMENT reuses gaps by default. Before MySQL 8.0, the counter reset on server restart when the table was empty.
• PostgreSQL (PostgreSQL docs): SERIAL creates a backing sequence object. GENERATED ALWAYS AS IDENTITY is the SQL:2003 standard equivalent and is preferred for new schemas.
• Oracle (Oracle docs): before 12c, sequences were separate objects fed into the column via a trigger or called explicitly in the INSERT statement.
• SQL Server (SQL Server docs): once a row with an identity value is inserted, you cannot insert an explicit value without SET IDENTITY_INSERT table ON.
• SQLite (SQLite docs): INTEGER PRIMARY KEY without the AUTOINCREMENT keyword reuses deleted values. Adding AUTOINCREMENT prevents reuse at a small performance cost.

String Types

String storage is one of the most divergent areas across databases, particularly around Unicode support and maximum lengths. Oracle's requirement for VARCHAR2 instead of VARCHAR trips up nearly every developer migrating from MySQL or PostgreSQL. SQL Server's distinction between VARCHAR (single-byte ASCII) and NVARCHAR (Unicode UTF-16) is equally easy to get wrong in production.

Important differences to be aware of:

• Oracle uses VARCHAR2, not VARCHAR. Oracle's documentation (Oracle SQL reference) reserves the VARCHAR keyword for potential future redefinition. Always use VARCHAR2.
• SQL Server uses N-prefix types for Unicode. 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 identical performance; the length limit is just a constraint, not a storage optimisation (PostgreSQL character types).
• SQLite ignores type affinity at the storage level. SQLite stores any string as TEXT regardless of whether you declare the column as VARCHAR(255) or CHAR(10). Type names are advisory only.

Numeric Types

Integer and decimal types are broadly consistent in naming, but gaps exist. Oracle stands out because it has no dedicated integer storage types — everything goes through the flexible NUMBER(p,s) format, which means integers may take more storage than the equivalent MySQL INT or PostgreSQL INTEGER. SQLite takes the opposite approach: the database decides actual storage size automatically based on the value stored, regardless of the declared type name.

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 to 8 bytes depending on value, regardless of the declared type name.
• MySQL supports UNSIGNED. INT UNSIGNED doubles the positive range to 0 through roughly 4.3 billion. No other major database supports this modifier.
• Use DECIMAL or NUMERIC for monetary values in all databases. Never use FLOAT or DOUBLE for money.

Which Databases Have a Native Boolean Type?

Boolean is one of the most inconsistent types across SQL databases. PostgreSQL is the only engine here with a true native BOOLEAN column that accepts TRUE, FALSE, and a range of equivalent string literals ('t', 'yes', 'on', and 1). SQL Server's BIT is close but is better described as a 1-bit integer. Oracle had no SQL-level boolean column at all until version 23c.

SQL Server's BIT accepts 1/0 and 'true'/'false' strings but is not a proper boolean type. Oracle's PL/SQL has a boolean type, but it cannot be used as a column type in SQL DDL prior to version 23c.

Date and Time Types

Date and time handling is where Oracle surprises developers most. Oracle's DATE type stores both date and time to the nearest second, unlike every other database where DATE is date-only. SQL Server's DATETIME2 (preferred over the deprecated DATETIME) offers 100-nanosecond precision and a wider year range. MySQL has no built-in timezone-aware timestamp type.

Critical Oracle gotcha: Oracle's DATE 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 deprecated the older DATETIME type in favour of DATETIME2, which has higher precision (100ns vs 3ms) and a wider date range. Use DATETIME2 in all new SQL Server schemas.

Which Databases Enforce CHECK Constraints?

CHECK constraints enforce rules at the database level — for example, limiting a status column to a fixed set of values. PostgreSQL, Oracle, and SQL Server have always enforced them fully. MySQL silently ignored CHECK constraints before version 8.0.16, released in April 2019 (MySQL CHECK constraint docs). Any schema built on MySQL 5.7 or earlier that relied on CHECK for data integrity may contain dirty data.

-- 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 in 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.
• PostgreSQL, Oracle, SQL Server: always enforced CHECK constraints fully.
• SQLite: enforced CHECK constraints from version 3.25.0, released 2018 (SQLite 3.25.0 release notes). Very old SQLite builds may ignore them.

DEFAULT Values

Specifying default values is mostly consistent across databases, but the function names for the current timestamp differ in every dialect. MySQL also has a unique extension that automatically refreshes a timestamp column on any UPDATE — no other database supports this without a trigger.

-- 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 all require a trigger to replicate this behaviour. SQLite has no trigger-free equivalent.

Generated (Computed) Columns

Generated columns derive their value from an expression evaluated automatically by the database. They're useful for storing pre-computed values like full names, order totals, or slugs without duplicating logic in application code. The syntax is similar across databases, but the STORED vs VIRTUAL distinction behaves differently depending on the engine.

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. SQLite has historically been the most restrictive — some operations still require recreating the table on older SQLite builds. Here are the most common operations across all five databases:

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 3.25.0+
ALTER TABLE users RENAME COLUMN phone TO phone_number;

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 3.35.0+ (March 2021)
ALTER TABLE users DROP COLUMN phone;

-- Older SQLite — not supported. Recreate the table.

Summary: Which Differences Matter Most in Practice

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

• 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 when 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 suited 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 MySQL, PostgreSQL, SQLite, Oracle, SQL Server, and Microsoft Access dialects and exports ready-to-run CREATE TABLE scripts — see the free online database designer or try the demo with your own schema.