Generating unique, sequential identifier numbers for relational rows is a cornerstone of database design. However, the syntax and engine mechanics for implementing auto-incrementing primary keys vary considerably between database systems.

A developer moving between SQLite, PostgreSQL, and MySQL can easily encounter subtle traps - from un-reclaimed ID recycling in SQLite to out-of-sync sequence counters in PostgreSQL after bulk data migrations.

In this tutorial, we analyze the architectural differences between SQLite’s rowid mechanics, PostgreSQL’s legacy SERIAL vs modern IDENTITY standards, and demonstrate how to manage primary keys visually in native macOS tools.

1. SQLite Auto-Increment Mechanics: INTEGER PRIMARY KEY vs AUTOINCREMENT

In SQLite, every table (unless created with WITHOUT ROWID) inherently possesses an internal 64-bit signed integer called the rowid.

The Standard Pattern:

CREATE TABLE users (
    id INTEGER PRIMARY KEY, -- Automatically aliases the internal rowid!
    username TEXT NOT NULL
);

When an id is defined as INTEGER PRIMARY KEY:

  • SQLite automatically populates it with the next largest integer.
  • If you delete rows and the maximum ID was not the largest possible 64-bit integer, SQLite may reuse previously deleted ID values.

When to Use the Explicit AUTOINCREMENT Keyword:

CREATE TABLE payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    amount_cents INTEGER NOT NULL
);

Using the explicit keyword AUTOINCREMENT enforces that IDs never decrease or reuse discarded numbers. SQLite creates an internal tracking table called sqlite_sequence to record the highest ID ever assigned.

[!TIP] Use INTEGER PRIMARY KEY for general application tables to save CPU overhead. Use AUTOINCREMENT specifically for audit logs, order invoices, or legal records where gap reuse is strictly prohibited.

2. PostgreSQL: Legacy SERIAL vs Modern IDENTITY

In PostgreSQL, sequence generation has undergone a major evolutionary shift.

The Legacy Approach: SERIAL / BIGSERIAL

-- Legacy syntax (PostgreSQL < 10)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL
);

Under the hood, SERIAL is not a true data type; it is syntactic sugar that automatically executes:

  1. CREATE SEQUENCE orders_id_seq;
  2. Sets id INT NOT NULL DEFAULT nextval('orders_id_seq');
  3. Sets ALTER SEQUENCE orders_id_seq OWNED BY orders.id;

The Modern Standard: GENERATED ALWAYS AS IDENTITY

Since PostgreSQL 10, the ANSI SQL standard IDENTITY column is strongly recommended over SERIAL:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL
);

Why IDENTITY is Superior to SERIAL:

  • Strict Integrity: GENERATED ALWAYS prevents accidental manual overrides during INSERT statements unless the caller explicitly includes OVERRIDING SYSTEM VALUE.
  • Cleaner Permissions: Does not leak independent sequence objects that require separate schema permissions.
  • Standards Compliant: Matches Oracle and DB2 SQL standards.

3. Fixing the “Duplicate Key Violates Unique Constraint” Sequence Trap

A classic PostgreSQL bug occurs after importing rows with explicit IDs (such as during a database migration or seed script):

-- Manual insert of historical IDs
INSERT INTO orders (id, customer_id) VALUES (101, 4), (102, 5);

-- Next application insert fails!
-- ERROR: duplicate key value violates unique constraint "orders_pkey"

Because the explicit INSERT bypassed nextval(), the internal sequence counter remains at 1. The next transaction attempts to insert ID 2, but row 2 already exists!

The Solution: Align the Sequence with setval()

SELECT setval(
    pg_get_serial_sequence('orders', 'id'), 
    COALESCE(MAX(id), 1)
) 
FROM orders;

4. Managing Primary Keys and Sequences in SQLOZ (macOS Native)

Configuring sequence behaviors manually across multiple database engines can be tedious. SQLOZ simplifies primary key configuration through its native macOS Table & Schema Inspector.

SQLOZ Schema Designer and Table Column Inspector

What SQLOZ Delivers:

  1. Engine-Aware Primary Key Toggles: Inside the Table Editor, toggling “Primary Key” automatically applies the optimal engine default (INTEGER PRIMARY KEY for SQLite, BIGINT GENERATED ALWAYS AS IDENTITY for PostgreSQL, BIGINT AUTO_INCREMENT for MySQL).
  2. One-Click Sequence Reset: Right-click any table in SQLOZ and select Reset Sequence / Auto-Increment. SQLOZ queries the active MAX(id) and issues the appropriate setval() or ALTER TABLE ... AUTO_INCREMENT command automatically.
  3. Cross-Dialect Migration Safety: When migrating from MySQL to PostgreSQL or SQLite using SQLOZ’s Cross-Dialect Migration Tool, sequences are automatically created and realigned to ensure zero duplicate key errors in production.

Summary

Choosing the right auto-increment strategy prevents integrity failures and simplifies schema portability. Use INTEGER PRIMARY KEY in SQLite, embrace GENERATED ALWAYS AS IDENTITY in modern PostgreSQL, and leverage tools like SQLOZ to automate sequence maintenance across your development workflow.

For more insights into cross-database migrations and schema management, read our guide on Migrating SQLite to PostgreSQL Without Scripts and discover How to Connect to Remote MySQL Databases Securely via SSH.