Generate SQL Primary Key Constraints from JSON

Primary Key Detection

Every well-designed SQL table needs a primary key. The converter analyzes your JSON to automatically detect and generate appropriate primary key constraints.

Example JSON

{
  "id": 1,
  "name": "Alice",
  "email": "alice@example.com",
  "department": "Engineering"
}

Generated SQL (Auto-increment)

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  department VARCHAR(255) NOT NULL
);

Detection Logic

The converter identifies primary keys by examining:

1. Column name — id, _id, pk, or {table_name}_id are strong indicators.
2. Value uniqueness — When multiple records are provided, the converter checks for unique values.
3. Data type — Integer or UUID values in an id column strongly suggest a primary key.

Auto-Increment Syntax

Database	Syntax
PostgreSQL	SERIAL or GENERATED ALWAYS AS IDENTITY
MySQL	INT AUTO_INCREMENT
SQLite	INTEGER PRIMARY KEY AUTOINCREMENT
SQL Server	INT IDENTITY(1,1)

UUID Primary Keys

When the JSON id field contains a UUID string:

{ "id": "550e8400-e29b-41d4-a716-446655440000" }

The converter generates:

-- PostgreSQL
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

-- MySQL
id CHAR(36) PRIMARY KEY

UUID keys are preferred in distributed systems because they can be generated on any node without coordination.

Composite Primary Keys

When no single column is a clear key but a combination is unique:

{ "user_id": 1, "role_id": 3, "granted_at": "2024-01-15" }

CREATE TABLE user_roles (
  user_id INTEGER NOT NULL,
  role_id INTEGER NOT NULL,
  granted_at TIMESTAMP NOT NULL,
  PRIMARY KEY (user_id, role_id)
);

Best Practices

• Prefer surrogate keys (auto-increment or UUID) over natural keys for primary keys.
• Always define a primary key — tables without one are a code smell.
• Use BIGINT for auto-increment keys on tables expected to exceed 2 billion rows.
• Consider UUIDv7 for time-sortable distributed IDs.