FK設定で詰まった話

こんにちはデータべースの設定で詰まったので、その時のメモを残しておきます。

テーブル名等に関しては、実際の値とは異なるものを記載しています。

前提

データベース: PostgreSQL v16.3
使用言語
- PHP v8.3
- Laravel v11

例

とある中間テーブルを作成していました。ここでは下記テーブルを例にして説明します。

ahiahi_somethingテーブル
- とある何かを表すテーブル
ahiahi_something_patternテーブル
- とある何かのパターンを表すテーブル
ahiahi_something_pattern_mappingテーブル
- とある何かがどのパターンであるかを表すテーブル
- パターンが複数あるため、中間テーブルを作成

構成

ahiahi_somethingテーブル
- ahiahi_something_id
- name
ahiahi_something_patternテーブル
- ahiahi_something_pattern_id
- name
ahiahi_something_pattern_mappingテーブル
- ahiahi_something_pattern_mapping_id
- FK: ahiahi_something_id
- FK: ahiahi_something_pattern_id

現象

ahiahi_something_pattern_mappingテーブルのmigrationファイルを作成し、migrateしようとしたところ、エラーが発生しました。

migrationファイルの一部抜粋

Schema::create('ahiahi_something_pattern_mapping', function (Blueprint $table) {
    $table->bigint('ahiahi_something_pattern_mapping_id')->primary()->autoIncrement();
    $table->uuid('ahiahi_something_id');
    $table->uuid('ahiahi_something_pattern_id');
    $table->timestamps();
    $table->unique(['ahiahi_something_id', 'ahiahi_something_pattern_id']);

    $table->foreign('ahiahi_something_id')
        ->references('ahiahi_something_id')
        ->on('ahiahi_something')
        ->onDelete('cascade');
    $table->foreign('ahiahi_something_pattern_id')
        ->references('ahiahi_something_pattern_id')
        ->on('ahiahi_something_pattern')
        ->onDelete('cascade');
});

エラーメッセージ

SQLSTATE[42710]: Duplicate object: 7 ERROR:
constraint "ahiahi_something_pattern_mapping_ahiahi_something_pattern_id_fo" for relation "ahiahi_something_pattern_mapping" already exists (
  Connection: pgsql, SQL: alter table "ahiahi_something_pattern_mapping"
  add constraint "ahiahi_something_pattern_mapping_ahiahi_something_pattern_id_foreign"
  foreign key ("ahiahi_something_pattern_id")
  references "ahiahi_something_pattern" ("ahiahi_something_pattern_id")
  on delete cascade
)

既に同名のFK制約が存在している...？してないが？？？という状況でした。

ちなみに、実際のテーブルを確認しても、FK制約は存在していませんでした。

SELECT constraint_name, table_schema
FROM information_schema.table_constraints
WHERE table_name = 'ahiahi_something_pattern_mapping'
  AND constraint_type = 'FOREIGN KEY';

↑ そもそもテーブル自体存在していないので、当然FK制約も存在しません。 table_nameの制約を外しても同じような制約は存在しませんでした。

原因

結果的には、FK制約などの識別子が63文字を超えてはいけないことが問題でした。今回は、パターンのFKが長すぎたためにエラーが発生していました。 "ahiahi_something_pattern_mapping_ahiahi_something_pattern_id_foreign" という名前が長すぎたのです。

エラーメッセージと原因が違いすぎて、無限に時間を溶かしました。。。

エラー分の最初が、constraint "ahiahi_something_pattern_mapping_ahiahi_something_pattern_id_fo" for relation と途中で切れていたので、そこに違和感をおぼえていれば、すに気づいたかもしれません。。。(誰が分かんねん)

dataGripを使用して、実際に発行されるSQL文を実行しようとしたところ、コンソール上で長すぎるよと警告が表示されたので、原因に気づくことができました。

解決策

今回は、例で言う ahiahi をprefixのように使っていたので、それを削除してFK制約の名前を短くしました。場合によっては、省略文字を使用して短くするのも良いかもしれませんが、個人的にはあまり好ましくはないです。

正直ベストプラクティスは全然分かりません。

まとめ

Postgresにおいて、識別子は63文字までという制約がある。
DBの設定で詰まったら生SQLを発行してみると良いかも。
エラーメッセージでおかしい箇所は疑ってみると良いかも。