Generate UNIQUE Constraints from JSON Data Patterns

Unique Constraint Detection

UNIQUE constraints ensure that no two rows have the same value in a column (or combination of columns). The converter detects uniqueness candidates by analyzing field names and sample data.

Example JSON

[
  { "id": 1, "username": "alice", "email": "alice@example.com", "name": "Alice" },
  { "id": 2, "username": "bob", "email": "bob@example.com", "name": "Bob" },
  { "id": 3, "username": "carol", "email": "carol@example.com", "name": "Carol" }
]

Generated SQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  CONSTRAINT uq_users_username UNIQUE (username),
  CONSTRAINT uq_users_email UNIQUE (email)
);

Detection Logic

The converter identifies unique columns through:

1. Name-based detection — Fields named email, username, slug, sku, code, ssn, or phone are common unique candidates.
2. Sample data analysis — When multiple records are provided, the converter checks if all values are distinct.
3. Combined heuristics — A column that has a known-unique name AND distinct sample values gets a UNIQUE constraint.

UNIQUE vs PRIMARY KEY

Feature	PRIMARY KEY	UNIQUE
NULL values	Not allowed	Allowed (one NULL in most DBs)
Per table	Only one	Multiple
Auto-creates index	Yes	Yes
Purpose	Row identity	Business rule enforcement

Composite Unique Constraints

Sometimes uniqueness applies to a combination of columns:

[
  { "user_id": 1, "role": "admin", "org_id": 10 },
  { "user_id": 1, "role": "viewer", "org_id": 10 },
  { "user_id": 1, "role": "admin", "org_id": 20 }
]

CONSTRAINT uq_user_org_role UNIQUE (user_id, role, org_id)

This ensures a user cannot have the same role in the same organization twice.

Partial Unique Constraints (PostgreSQL)

PostgreSQL supports conditional uniqueness:

CREATE UNIQUE INDEX uq_users_active_email
  ON users (email)
  WHERE is_active = TRUE;

This allows duplicate emails as long as only one row with that email is active — useful for soft-delete patterns.

Naming Convention

Use the pattern uq_{table}_{column(s)}. For composite constraints, join column names with underscores: uq_user_roles_user_id_org_id.