How can I link two tables in terms of foreign keys via laravel migrations

Question

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');
    });

Likely due to mismatch of data types? ... `id()` makes an 8-byte value, whereas `unsignedInteger()` makes a 4-byte value. For FK's, the data types must match in type AND signed-ness (assuming numeric, both signed or unsigned) to make it work. See [this answer](https://stackoverflow.com/a/4673775/7644018) for a more complete list of various things about FK's. — Paul T., Sep 24 '22 at 03:07
I think your problem is you're creating the relation backwards as how it should be. A thread has many messages, not otherwise (Idk, I may be wrong). Remove the foreign method in `thread` creation and alter the `message` migration to add another foreign key linked to the `thread` table — Manuel Guzman, Sep 24 '22 at 04:29
@ManuelGuzman I tried this but still getting this error: `SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `message` add constraint `message_thread_id_foreign` foreign key (`thread_id`) references `thread` (`id`) on delete cascade)`. — luvs2spooge, Sep 24 '22 at 11:12

Behnam · Answer 1 · 2022-09-24T06:10:11.917

1

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');
    });

edited Sep 24 '22 at 06:10

answered Sep 24 '22 at 05:52

Behnam

9
2

This seemed like the correct answer but I get this error: `General error: 1215 Cannot add foreign key constraint (SQL: alter table `message` add constraint `message_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade)`... not sure why. – luvs2spooge Sep 24 '22 at 11:00
@luvs2spooge That error can sometimes occur if the referencing table migration is exexucting before the referenced table migration. Ensure that your `thread` migration executes before `message`. – Peppermintology Sep 24 '22 at 11:38
@Peppermintology I ensured `thread` migration executes before `message` migration by switching the date time on the `thread` migration file but I'm still getting that same error for some reason. – luvs2spooge Sep 24 '22 at 13:06
@luvs2spooge I've just noticed the error is relating to the `Users` foreign key. What does the migration for your `users` table look like and it is run before your `message` migration? – Peppermintology Sep 24 '22 at 13:43
@Peppermintology I'v posted my user migration in my original post. The order of migrations are: 1. users 2. thread 3. message – luvs2spooge Sep 24 '22 at 13:48
@luvs2spooge `$table->increments('id');` is your problem, change it to `$table->id();` and try your migrations again. – Peppermintology Sep 24 '22 at 14:01

score 0 · Answer 2 · answered Sep 24 '22 at 08:51

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');
    });

score 0 · Answer 3 · answered May 01 '23 at 13:47

use this as an example to correct your code.

Laravel 6+ use bigIncrements(), so you need to use unsignedBigInteger() method change (if you have it):

        Schema::create('subcounties', function (Blueprint $table) {
            $table->id();
            $table->Integer('countyid'); //change this line
            $table->string('subcounty');
            $table->timestamps();

            $table->foreign('countyid')->references('id')
                  ->on('counties')
                  ->onDelete('cascade');
        });
    }

change it to this:

        Schema::create('subcounties', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('countyid')->nullable(); //change it to this
        $table->string('subcounty');
        $table->timestamps();

        $table->foreign('countyid')->references('id')->on('counties')->onDelete('cascade');

    });
}

How can I link two tables in terms of foreign keys via laravel migrations

3 Answers3