Laravel 電話番号で外部キー制約

Question

概要

Laravelのマイグレーションファイルにて、自動で$table->id();のようなカラムを外部キー制約として行う事ができたのですが、例えば電話番号のようなもので外部キー制約を行いたいのですがうまくいきません。

実現したいこと

実現したい内容はidを外部キー制約元とするのではなく、tel_numを外部キー制約元にしてtel_idと紐付けを行いたいです。

users_table
 - id      # 自動ID
 - name    # ユーザ名 unsignedBigIntegerを使用
 - tel_num # 電話番号(*1)

profiles_tabel
 - id      # 自動ID
 - tel_id  # 電話番号(*1と外部キー制約) unsignedBigIntegerを使用

users_tabel

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->unsignedBigInteger('tel_num')->nullable()->default(null);
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

profiles_tabel

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateProfilesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('profiles', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('tel_id')->unique();   
            $table->foreign('tel_id')->references('tel_num')->on('users');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('profiles');
    }
}

調べたとこと型が違った場合エラーになっている事が原因としてあげられると書いてあったのですが型を調べたところ両方とも上記コードで指定しているようにunsignedBigIntegerを指定しており、できていると思っております。

エラー内容

  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `profiles` add constraint `profiles_primary_id_foreign` foreign key (`tel_id`) references `users` (`tel_num`))

環境

Laravel 6.xを使用しています。

Comments

[m.ts10806]

テーブル定義、ER図を提示してください。
エラーはSQLのものですし、調べれば原因はすぐわかりますが、
定義次第でやり方も変わってくると思います。あとどういうデータ取りたいかとか。
あと電話番号が外部キーという設計があまり見えません。

[退会済みユーザー]

今回の質問につきましては、何か開発していてではなく勉強のため行っているので他のカラムはありません。
ですので簡単なテーブル定義について追記させていただきます。

[m.ts10806]

SQLSTATEのエラーについては調べられましたか？ここから大体わかると思うのですが（DBについて多少知見があれば）

[退会済みユーザー]

はい。既にカラムが存在しているといエラーだと認識しています。

[hayato7]

foreignIdとforeignの違いについて調べるのがよいかと思います。

[退会済みユーザー]

個人的には以下のコードに問題があると考えています。ただそれにあったDoc, 記事が見当たらず。。
予想ですが、$table->foreignと挙動が違い$table->foreignIdだけでカラムが作成されるため、エラーが発生してしまっていると考えています。
$table->foreignId('tel_num')->references('tel_num')->on('users')->onDelete('cascade');

[m.ts10806]

＞既にカラムが存在しているといエラーだと認識しています。

違います。
「重複したカラム名」
今回のように同じカラム名で別々のテーブルを結合した上で、そのカラム名を参照（SELECT句に入れる）しようとしているときに起きます。
同じカラム名なので、参照したいなら別名をつける必要があります。
参照しないならSELECT句に含めないことです。

[退会済みユーザー]

foreignIdではidを外部キー制約としていないのでこちらを使用しませんでした。
エラーを見た感じでは命名にも問題があるのでしょうか？

[退会済みユーザー]

--

[phper.k]

「調べたとこと型が違った場合エラーになっている事が原因としてあげられると書いてあったのですが型を調べて上記あっているはずです！ 」
型があっているなら、外部キー制約はつけられるはずです。

初心者の「〜なはず」は残念ながら、信用に値しません。回答には確実な事実が必要です。エビデンスを示しましょう。

[退会済みユーザー]

ありがとうございます。
追記させていただきました。

[phper.k]

実際のテーブル定義を提示しましょう。
マイグレーションを出したところで、実際のテーブルが正しく定義されたとは言い切れません。

show create table table_name; で、create 文が表示されます。

[退会済みユーザー]

記載しました。ご確認お願いします。

[phper.k]

ちゃんと指示通りのことをしてください

[退会済みユーザー]

show create table table_name;はどこで実行すれば良いでしょうか？

[m.ts10806]

MySQLのコマンドライン

[退会済みユーザー]

php artisan migrateを行ったのですがどうやらエラーが発生し、usersテーブルしか作成されていないようです。。
usersテーブルの実行結果も提示した方が良いでしょうか？

[phper.k]

php artisan migrate:fresh で再作成してください。

というか、会話を成立させるつもりがないんですね。

Answer 1

手元(myql 8.0.22)で試してみましたがエラーが少し違って

  SQLSTATE[HY000]: General error: 1822 Failed to add the foreign key constraint. Missing index for constraint 'profiles_tel_id_foreign' in the referenced table 'users' (SQL: alter table `profiles` add constraint `profiles_tel_id_foreign` foreign key (`tel_id`) references `users` (`tel_num`))

になりました。これならわかりやすくて、usersテーブル(のtel_numカラム)にインデックスがないからだめだと言っています。

理由は13.1.17.2 外部キー制約の使用に書いてあるようにインデックスがないと効率が悪くなるからです。

MySQL では、外部キーチェックを高速に実行でき、かつテーブルスキャンが必要なくなるように、外部キーおよび参照されるキーに関するインデックスが必要です。

このエラーは古いバージョンだと1215にまとめられていたのですが、エラーメッセージから原因がわかりにくかったので、どこかの時点で改良されました。