Generate Foreign Key Constraints from JSON References

Foreign Key Detection

When JSON data contains fields that reference other entities (e.g., user_id, category_id), the converter can generate foreign key constraints that enforce referential integrity.

Example JSON

{
  "id": 101,
  "title": "Introduction to SQL",
  "author_id": 5,
  "category_id": 3,
  "published_at": "2024-03-15T10:00:00Z"
}

Generated SQL

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author_id INTEGER NOT NULL,
  category_id INTEGER NOT NULL,
  published_at TIMESTAMP WITH TIME ZONE NOT NULL,
  CONSTRAINT fk_articles_author FOREIGN KEY (author_id) REFERENCES authors(id),
  CONSTRAINT fk_articles_category FOREIGN KEY (category_id) REFERENCES categories(id)
);

Detection Heuristics

The converter identifies foreign key candidates by:

1. Naming pattern — Columns ending in _id where the prefix matches a known table name (e.g., author_id → authors.id).
2. Integer type — Foreign keys are typically integers referencing auto-increment primary keys.
3. Multiple records — Repeated values across records suggest a reference to a lookup table.

ON DELETE Actions

-- Prevent deletion of referenced parent
CONSTRAINT fk_articles_author FOREIGN KEY (author_id)
  REFERENCES authors(id) ON DELETE RESTRICT

-- Set to NULL when parent is deleted
CONSTRAINT fk_articles_category FOREIGN KEY (category_id)
  REFERENCES categories(id) ON DELETE SET NULL

-- Delete child when parent is deleted
CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
  REFERENCES orders(id) ON DELETE CASCADE

Action	Use when
RESTRICT	Deleting parent should be blocked
CASCADE	Children are meaningless without parent
SET NULL	Children can exist independently
SET DEFAULT	Reassign to a default parent

Naming Convention

Use a consistent pattern: fk_{child_table}_{column} or fk_{child_table}_{parent_table}. This makes it easy to identify constraints in error messages and migration scripts.

Self-Referencing Keys

Some tables reference themselves:

{ "id": 5, "name": "Engineering", "parent_id": 2 }

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  parent_id INTEGER NULL,
  CONSTRAINT fk_departments_parent FOREIGN KEY (parent_id) REFERENCES departments(id)
);

This models tree structures like organizational hierarchies, category trees, and comment threads.

Index Recommendation

Foreign key columns should always have an index. PostgreSQL does not automatically create one (unlike MySQL/InnoDB), so the converter adds a CREATE INDEX statement for each foreign key.