JSON参照からSQL外部キー制約を生成する

外部キーの検出

JSONデータに他のエンティティを参照するフィールド（例：user_id、category_id）が含まれる場合、コンバーターは参照整合性を強制する外部キー制約を生成できます。

JSON例

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

生成される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)
);

検出ヒューリスティック

コンバーターは以下の方法で外部キー候補を識別します：

1. 命名パターン — _idで終わるカラムで、プレフィックスが既知のテーブル名と一致する場合（例：author_id → authors.id）。
2. 整数型 — 外部キーは通常、オートインクリメントの主キーを参照する整数です。
3. 複数レコード — レコード間で繰り返される値は、ルックアップテーブルへの参照を示唆します。

ON DELETEアクション

-- 参照される親の削除を防止
CONSTRAINT fk_articles_author FOREIGN KEY (author_id)
  REFERENCES authors(id) ON DELETE RESTRICT

-- 親が削除された場合NULLに設定
CONSTRAINT fk_articles_category FOREIGN KEY (category_id)
  REFERENCES categories(id) ON DELETE SET NULL

-- 親が削除された場合子も削除
CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
  REFERENCES orders(id) ON DELETE CASCADE

アクション	使用場面
RESTRICT	親の削除をブロックすべき場合
CASCADE	子は親なしでは意味がない場合
SET NULL	子は独立して存在できる場合
SET DEFAULT	デフォルトの親に再割り当てする場合

命名規則

一貫したパターンを使用してください：fk_{子テーブル}_{カラム}またはfk_{子テーブル}_{親テーブル}。これにより、エラーメッセージやマイグレーションスクリプトで制約を簡単に識別できます。

自己参照キー

一部のテーブルは自分自身を参照します：

{ "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)
);

これは組織階層、カテゴリツリー、コメントスレッドなどのツリー構造をモデル化します。

インデックスの推奨

外部キーカラムには常にインデックスを作成すべきです。PostgreSQLは自動的に作成しません（MySQL/InnoDBとは異なり）。コンバーターは各外部キーに対してCREATE INDEX文を追加します。