Convert JSON Enum Values to SQL CHECK Constraints

Enum Detection and Constraint Generation

When a JSON field contains values from a fixed set (like "active", "inactive", "suspended"), the converter generates a SQL constraint that restricts the column to those values.

Example JSON

[
  { "id": 1, "name": "Alice", "status": "active", "role": "admin" },
  { "id": 2, "name": "Bob", "status": "inactive", "role": "editor" },
  { "id": 3, "name": "Carol", "status": "active", "role": "viewer" },
  { "id": 4, "name": "Dave", "status": "suspended", "role": "viewer" }
]

Generated SQL (CHECK constraint)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  status VARCHAR(20) NOT NULL,
  role VARCHAR(20) NOT NULL,
  CONSTRAINT chk_users_status CHECK (status IN ('active', 'inactive', 'suspended')),
  CONSTRAINT chk_users_role CHECK (role IN ('admin', 'editor', 'viewer'))
);

Generated SQL (ENUM type — MySQL/PostgreSQL)

-- PostgreSQL
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');
CREATE TYPE user_role AS ENUM ('admin', 'editor', 'viewer');

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  status user_status NOT NULL,
  role user_role NOT NULL
);

Detection Logic

The converter identifies enum candidates by:

1. Low cardinality — A string column with fewer than 10-15 unique values across all sample records.
2. Short values — Values under 30 characters (long strings are usually free-text).
3. Repeated patterns — The same values appear across multiple records.

CHECK vs ENUM

Feature	CHECK constraint	ENUM type
Portability	All databases	PostgreSQL, MySQL
Adding values	Alter CHECK	ALTER TYPE ADD VALUE (PG)
Type safety	Column is still VARCHAR	Dedicated type
Ordering	Alphabetical	Definition order
Multiple tables	Repeat CHECK per table	Reuse type

Best Practice Recommendation

• PostgreSQL — Use CREATE TYPE ... AS ENUM for reusable enums.
• MySQL — Use column-level ENUM('a','b','c') or CHECK constraints (MySQL 8.0+).
• SQL Server — Use CHECK constraints (no native ENUM).
• Portable SQL — Use CHECK constraints everywhere.

Migration Considerations

Adding a new value to a CHECK constraint requires replacing the constraint:

ALTER TABLE users DROP CONSTRAINT chk_users_status;
ALTER TABLE users ADD CONSTRAINT chk_users_status
  CHECK (status IN ('active', 'inactive', 'suspended', 'archived'));

PostgreSQL ENUM is easier — ALTER TYPE user_status ADD VALUE 'archived' — but you cannot remove values.

When Not to Use

If the set of values changes frequently or is user-defined (e.g., custom tags), use a foreign key to a lookup table instead. ENUM/CHECK is best for stable, well-known value sets.