I'm trying to link the thread table to the message table but when migrating, I get an error that says:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `thread` add constraint `thread_id_foreign` foreign key (`id`) references `message` (`thread_id`))

What am I doing wrong and how can I achieve this?

users migration:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('email')->unique();
        $table->string('full_name');
        $table->string('password');
        $table->string('bio');
        $table->rememberToken();
        $table->timestamps();
    });
}

Here's the thread migration:

   Schema::create('thread', function (Blueprint $table) {
        $table->id();
        $table->string('title');
        $table->foreign('id')
            ->references('thread_id')
            ->on('message');
    });

Here's message migration:

    Schema::create('message', function (Blueprint $table) {
        $table->id();
        $table->unsignedInteger('user_id');
        $table->unsignedInteger('thread_id');
        $table->string('body');
        $table->foreign('user_id')
            ->references('id')
            ->on('users')
            ->onDelete('cascade');
    });

CodePudding user response：

You need to use unsignedBigInteger for foreign key column's

And you should set foreign key for child table not parent table

Try this:

Schema::create('thread', function (Blueprint $table) {
        $table->id();
        $table->string('title');
    });

Schema::create('message', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('user_id');
        $table->unsignedBigInteger('thread_id');
        $table->string('body');


        $table->foreign('user_id')
            ->references('id')
            ->on('users')
            ->onDelete('cascade');

        $table->foreign('thread_id')
            ->references('id')
            ->on('thread')
            ->onDelete('cascade');
    });

CodePudding user response：

if you are linking the thread table to the message use it like this

Note: make sure that you create the message migration first then the thread migration

    Schema::create('message', function (Blueprint $table) {
        $table->id();
        // $table->unsignedInteger('thread_id');-> No Need for this column in here
        $table->string('body');
        $table->foreignId('user_id')->constrained('users')->onDelete('cascade');
    });


   Schema::create('thread', function (Blueprint $table) {
        $table->id();
        $table->string('title');
        $table->foreignId('message_id')->constrained('message')->onDelete('cascade');
    });

but if you want to link the message table to thread table than use it like this

   Schema::create('thread', function (Blueprint $table) {
        $table->id();
        $table->string('title');
    });

    Schema::create('message', function (Blueprint $table) {
        $table->id();
        $table->foreignId('thread_id')->constrained('thread')->onDelete('cascade');
        $table->string('body');
        $table->foreignId('user_id')->constrained('users')->onDelete('cascade');
    });