CODEWITHSUNDEEP
laravelreferencemigration
Colby McHenry
Colby McHenry

Reputation: 78

Laravel 7 General error: 1215 Cannot add foreign key constraint

I've been researching this for hours, can't seem to solve it. Here is the error

General error: 1215 Cannot add foreign key constraint (SQL: alter table 'users' add constraint 'users_discord_id_foreign' foreign key ('discord_id') references 'discord_o_auths' ('id'))

Here is my DiscordOAuths Migration:

public function up()
{
    Schema::create('discord_o_auths', function (Blueprint $table) {
        $table->engine = "InnoDB";
        $table->integer('id')->unique();
        $table->string('access_token')->unique();
        $table->string('refresh_token')->unique();
        $table->bigInteger('token_expiration');
        $table->timestamps();
    });
}

And here is my Users Migration. 

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->engine = "InnoDB";
        $table->id();
        $table->boolean('admin')->default(false);
        $table->integer('color_scheme')->default(0);
        $table->rememberToken();
        $table->timestamps();
    });

    Schema::table('users', function($table) {
        $table->engine = "InnoDB";
        $table->integer('discord_id')->unsigned();
        $table->foreign('discord_id')->references('id')->on('discord_o_auths');
    });
}

The DiscordOAuths table is created before the Users table as well. What am I doing wrong?

Upvotes: 0

Views: 76

Answers (1)

César Escudero
César Escudero

Reputation: 329

You're trying to assign a foreign key constraint to an integer column, which is not the same as the unsigned columns that Laravel uses for its id's.

In your DiscordOAuths you have to replace $table->integer('id')->unique(); with $table->bigIncrements('id'); or the new method added in Laravel 7 $table->id(); which is an alias of the previous one.

Your discord_id column in the users migration also needs to be an unsigned column. You have to define it using either $table->unsignedBigInteger('discord_id'); or its alias that came with Laravel 7: $table->foreignId('discord_id');

So your migrations would look like this:

For DiscordOAuths

public function up()
{
    Schema::create('discord_o_auths', function (Blueprint $table) {
        $table->engine = "InnoDB";
        $table->id();
        $table->string('access_token')->unique();
        $table->string('refresh_token')->unique();
        $table->bigInteger('token_expiration');
        $table->timestamps();
    });
}

And for users it would be like this:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->engine = "InnoDB";
        $table->id();
        $table->boolean('admin')->default(false);
        $table->integer('color_scheme')->default(0);
        $table->rememberToken();
        $table->timestamps();
    });

    Schema::table('users', function($table) {
        $table->engine = "InnoDB";
        $table->foreignId('discord_id');
        $table->foreign('discord_id')->references('id')->on('discord_o_auths');
    });
}

Upvotes: 1

Related Questions