Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint #27717

Closed
jackwander opened this Issue Feb 28, 2019 · 20 comments

Comments

Projects
None yet
@jackwander
Copy link

jackwander commented Feb 28, 2019

  • Laravel Version: 5.8.1
  • PHP Version: 7.1
  • Database Driver & Version: MySQL 5.7.22-0ubuntu0.17.10.1

Description:

My code for inserting FK is working on 5.7 but not in 5.8.

@driesvints

This comment has been minimized.

Copy link
Member

driesvints commented Feb 28, 2019

I'll need more info and/or code to debug this further. Please post relevant code like models, jobs, commands, notifications, events, listeners, controller methods, routes, etc. You may use https://paste.laravel.io to post larger snippets or just reply with shorter code snippets. Thanks!

@jackwander

This comment has been minimized.

Copy link
Author

jackwander commented Feb 28, 2019

I'll need more info and/or code to debug this further. Please post relevant code like models, jobs, commands, notifications, events, listeners, controller methods, routes, etc. You may use https://paste.laravel.io to post larger snippets or just reply with shorter code snippets. Thanks!

Sorry my bad.

Here is the sequence of my migration.

Users Table

        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('fname');
            $table->string('mname');
            $table->string('lname');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

Roles

        Schema::create('roles', function (Blueprint $table) {
            $table->increments('id');
            $table->string('role');
            $table->timestamps();
        });

Inserting FK to Users

        Schema::table('users', function (Blueprint $table) {
            $table->unsignedInteger('role_id')->nullable();
            $table->foreign('role_id')
                ->references('id')
                ->on('roles');
        });
@staudenmeir

This comment has been minimized.

Copy link
Contributor

staudenmeir commented Feb 28, 2019

Are both tables using the InnoDB engine?

@driesvints

This comment has been minimized.

Copy link
Member

driesvints commented Feb 28, 2019

What error are you getting exactly?

@jackwander

This comment has been minimized.

Copy link
Author

jackwander commented Feb 28, 2019

Are both tables using the InnoDB engine?

Yes they're both InnoDB engine.

What error are you getting exactly?

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

What's the difference of bigIncrements to increments? Coz in 5.7 the id per migration create is increments but in 5.8 it's bigIncrements.

@driesvints

This comment has been minimized.

Copy link
Member

driesvints commented Feb 28, 2019

@michaeldzjap

This comment has been minimized.

Copy link
Contributor

michaeldzjap commented Mar 1, 2019

I am running into the same problem here. I have a migration that adds a foreign key, referencing the id column on the users table and after upgrading to Laravel 5.8 this no longer works. This is the up of my original migration:

public function up()
{
    Schema::create('two_factor_auths', function (Blueprint $table) {
        $table->string('id')->nullable();
        $table->unsignedInteger('user_id');
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        $table->timestamps();
    });
}

But then running php artisan migrate -v on a freshly created db (InnoDB) results in:

  Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade)

  at /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668| 

  Exception trace:

  1   PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  2   PDOStatement::execute()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  3   Illuminate\Database\Connection::Illuminate\Database\{closure}("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade", [])
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:657

  4   Illuminate\Database\Connection::runQueryCallback("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade", [], Object(Closure))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624

  5   Illuminate\Database\Connection::run("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade", [], Object(Closure))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:459

  6   Illuminate\Database\Connection::statement("alter table `two_factor_auths` add constraint `two_factor_auths_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade")
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php:97

  7   Illuminate\Database\Schema\Blueprint::build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:264

  8   Illuminate\Database\Schema\Builder::build(Object(Illuminate\Database\Schema\Blueprint))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:165

  9   Illuminate\Database\Schema\Builder::create("two_factor_auths", Object(Closure))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:237

  10  Illuminate\Support\Facades\Facade::__callStatic("create")
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/michaeldzjap/twofactor-auth/src/database/migrations/2017_05_26_102832_create_two_factor_auths_table.php:21

  11  CreateTwoFactorAuthsTable::up()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:360

  12  Illuminate\Database\Migrations\Migrator::Illuminate\Database\Migrations\{closure}()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:367

  13  Illuminate\Database\Migrations\Migrator::runMigration(Object(CreateTwoFactorAuthsTable), "up")
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:178

  14  Illuminate\Database\Migrations\Migrator::runUp("/home/vagrant/code/laravel-two-factor-authentication-example/vendor/michaeldzjap/twofactor-auth/src/database/migrations/2017_05_26_102832_create_two_factor_auths_table.php")
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:147

  15  Illuminate\Database\Migrations\Migrator::runPending([])
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:96

  16  Illuminate\Database\Migrations\Migrator::run([])
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php:71

  17  Illuminate\Database\Console\Migrations\MigrateCommand::handle()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32

  18  call_user_func_array([])
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:32

  19  Illuminate\Container\BoundMethod::Illuminate\Container\{closure}()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:90

  20  Illuminate\Container\BoundMethod::callBoundMethod(Object(Illuminate\Foundation\Application), Object(Closure))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:34

  21  Illuminate\Container\BoundMethod::call(Object(Illuminate\Foundation\Application), [])
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Container/Container.php:580

  22  Illuminate\Container\Container::call()
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Console/Command.php:183

  23  Illuminate\Console\Command::execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Command/Command.php:255

  24  Symfony\Component\Console\Command\Command::run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Console/Command.php:170

  25  Illuminate\Console\Command::run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Application.php:901

  26  Symfony\Component\Console\Application::doRunCommand(Object(Illuminate\Database\Console\Migrations\MigrateCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Application.php:262

  27  Symfony\Component\Console\Application::doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/symfony/console/Application.php:145

  28  Symfony\Component\Console\Application::run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Console/Application.php:90

  29  Illuminate\Console\Application::run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:122

  30  Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
      /home/vagrant/code/laravel-two-factor-authentication-example/artisan:37

Changing from $table->unsignedInteger('user_id'); to $table->bigIncrements('user_id'); in my migration makes it work. I can then successfully run the migration. Maybe this will help.

@mfn

This comment has been minimized.

Copy link
Contributor

mfn commented Mar 1, 2019

I think it's related to:

Maybe I'm mistaken, but these are the only changes which come up to me as having the potential creating a column type mismatch between two tables.

@michaeldzjap

This comment has been minimized.

Copy link
Contributor

michaeldzjap commented Mar 1, 2019

What would be the advice for people upgrading from < 5.8 to 5.8 though? Should they ideally change their id col on users to bigIncrements? Or should / can they safely keep using increments?

And with regards to libraries that use migrations that reference id on users this becomes a little complicated as well. If I don't update the affected migration then my library becomes incompatible for users that start a fresh Laravel 5.8 project. If I do update the affected migration my library becomes incompatible for users that upgrade from < 5.8 to 5.8 (and hence don't change from increments to bigIncrements) and decide they want to start using my lib (hence having to run the original migration which now uses bigIncrements). Not seeing a clean solution out of this. Perhaps someone has an idea?

@ekam230

This comment has been minimized.

Copy link

ekam230 commented Mar 1, 2019

I have this trouble too.

i fix this trouble

change in migration users

Schema::create('users', function (Blueprint $table) {
$table->BigIncrements('id');

on

Schema::create('users', function (Blueprint $table) {
$table->increments('id');

@michaeldzjap

This comment has been minimized.

Copy link
Contributor

michaeldzjap commented Mar 1, 2019

Coming back to this. I've managed to sort of solve my problems, but it feels very hacky. I basically check the type of id on users before creating my user_id column:

if (DB::getDoctrineSchemaManager()->listTableDetails('users')->getColumn('id')->getType() instanceof \Doctrine\DBAL\Types\IntegerType) {
    $table->increments('user_id');
} else {
    $table->bigIncrements('user_id');
}

This is not great because:

  1. There now is a dependency on the doctrine/dbal package. It was the only easy way I could figure out to get the type of the id column on users. If someone else knows another way, please share.
  2. I don't think it is appropriate to use this sort of conditional logic in a migration file, but I see no other solution.

I realise that this is perhaps not going to be received well, but is there any way this bigIncrements change on users can be reverted? I have read the provided links by @mfn and @driesvints, and I can understand why the change was considered. However, I also think perhaps the consequences of this change on an existing code base have been somewhat miscalculated.

@jackwander

This comment has been minimized.

Copy link
Author

jackwander commented Mar 2, 2019

Hi guys, I've solved this by changing all my migration's id from bigIncrements to increments. So every time i generate a new migration php artisan make:migration NAME --create=table_name i just change the bigIncrements to increments so that i can set a FK constraint.

@devcircus

This comment has been minimized.

Copy link
Contributor

devcircus commented Mar 2, 2019

or change your foreign keys to bigintegers. This is in line with where the framework is headed.

@heloufir

This comment has been minimized.

Copy link

heloufir commented Mar 4, 2019

Hello,
I just had the same issue with the laravel 5.8.2 version, I did what @jackwander says

"I've solved this by changing all my migration's id from bigIncrements to increments"

To make it works, just for your information, I changed my foreign keys to bigInteger and unsignedBigInteger but it doesn't work either.

@snetts

This comment has been minimized.

Copy link

snetts commented Mar 4, 2019

Hello,
What worked for me is changing from the column from integer to bigInteger and placing it in the table update section as follows:

Schema::table('services', function($table) { $table->bigInteger('business_id')->unsigned()->index(); $table->foreign('business_id')->references('id')->on('businesses')->onDelete('cascade'); });

Thanks for you help guys.

@driesvints

This comment has been minimized.

Copy link
Member

driesvints commented Mar 28, 2019

I'm closing this because as noted, foreign keys need to be of the same type. Please see the upgrade guide: https://laravel.com/docs/5.8/upgrade

@driesvints driesvints closed this Mar 28, 2019

@fridzema

This comment has been minimized.

Copy link

fridzema commented Mar 29, 2019

At the moment of release this was not documented in the release notes...
Why is it even changed?

~99% of the people don't fill the database with 2147483647 records.
When you go above this amount i hope you know what you are doing and can easily change this yourself.

Now it is default for all users with zero advantages in my opinion.

Some examples why this is so bad:
http://ronaldbradford.com/blog/bigint-v-int-is-there-a-big-deal-2008-07-18/
https://www.mssqltips.com/sqlservertip/5115/compare-sql-server-table-join-performance-for-int-vs-bigint-vs-guid-data-types/

@arabrain

This comment has been minimized.

Copy link

arabrain commented Mar 29, 2019

Hello,
Because they new version of Laravel using by default use BIGINT for the primary key"id", then to resolve the problem of foreign key you need to create the same type in the child table.

public function up()
{
Schema::create('yourTableName', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('fieldName');
/**
*The foregin Key take the same type of primary key for the mother table (user)
*/
$table->unsignedBigInteger('user_id');

        $table->timestamps();

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

I hope this help you and thanks ;)

@nmichalakis

This comment has been minimized.

Copy link

nmichalakis commented Apr 17, 2019

@arabrain solution did the trick (thank you!). I have lost like 2hrs wandering about what i was doing wrong and as a new laravel guy this was really frustrating.

@prateekvarma

This comment has been minimized.

Copy link

prateekvarma commented Apr 20, 2019

It's just a datatype conflict. Either change your bigIncrements to increments... or change your foreign key from integer to bigInteger

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.