General error: 1215 Cannot add foreign key constraint

Well the migration are created based on the file name. So based on the timestamp that's in front of it. If you want a migration to go before another migration you need to either update the filename and lower the timestamp or create the new migrations in the correct order!

@bobbybouwmann this is what i did. I changed the timestamp of the payment_system to be first and then the payment_currencies but is not working

What is not working?

i'm still getting the same error message, is not executing first the payment_systems and after the payment_currencies

@giwrgos I had the same issue when running L5.0 application on homestead (whether other laravel versions and environments are affected is unknown).

The issue was that either mysql didn't want foreign keys during table creation, or laravel was issuing them in the wrong order.

In short, this didn't work:

        Schema::create('cart_items', function(Blueprint $table)
        {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('code', 16)->index();

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

            $table->integer('quantity')->unsigned();
            $table->timestamps();
        });

This worked:

        Schema::create('cart_items', function(Blueprint $table)
        {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('code', 16)->index();
            $table->integer('user_id')->unsigned();
            $table->integer('product_id')->unsigned();
            $table->integer('quantity')->unsigned();
            $table->timestamps();
        });
        Schema::table('cart_items', function(Blueprint $table)
        {
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
        });

@constb I tried it but is not working,

Here are my files

filename: 2015_06_25_153736_payment_systems.php

Contains:

    public function up()
{
    Schema::create('payment_systems', function(Blueprint $table)
    {
        $table->bigIncrements('id_payment_system');
        $table->string("title",100)->collate('utf8_general_ci');
        $table->string("code",10)->collate('utf8_general_ci');
        $table->string("type",4)->collate('utf8_general_ci');
        $table->boolean("active")->default(true);
        $table->timestamps();
        $table->softDeletes();
        $table->unique('title');
        $table->unique('code');
    });
}

filename: 2015_06_25_153743_payment_currencies.php contains:

    public function up()
{
    Schema::create('payment_currencies', function(Blueprint $table)
    {
        $table->bigIncrements('id_payment_currencies');
        $table->integer('payment_system_id_fn')->unsigned();
        $table->integer('opening_balance_id_fn')->unsigned();
        $table->boolean("active")->default(true);
        $table->timestamps();
        $table->softDeletes();
        //$table->foreign('payment_system_id_fn')->references('id_payment_system')->on('payment_systems');
        //$table->foreign('opening_balance_id_fn')->references('id_opening_balance')->on('opening_balance');

    });

    Schema::table('payment_currencies', function(Blueprint $table)
    {
        $table->foreign('payment_system_id_fn')->references('id_payment_system')->on('payment_systems');
    });
}

@giwrgos 1) try adding $table->engine = 'InnoDB'; - MyISAM doesn't support foreign keys and sometimes it's the default. 2) check field types, payment_systems.id_payment_system is bigint while payment_currencies.payment_system_id_fn is unsigned int - consider replacing bigIncrements with increments.

I have changed it but now I'm getting a different error message

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table def
inition; there can be only one auto column and it must be defined as a key
(SQL: create table platforms (id_platform_details int unsigned not null
auto_increment primary key, title varchar(100) not null, host varchar(

100. not null, port int not null auto_increment primary key, active tin
     yint(1) not null default '0', created_at timestamp default 0 not null, u pdated_at timestamp default 0 not null, deleted_at timestamp null) defau
     lt character set utf8 collate utf8_unicode_ci engine = InnoDB)

where my function is this one

public function up() { Schema::create('platforms', function(Blueprint $table) { $table->engine = 'InnoDB'; $table->increments('id_platform_details'); $table->string("title",100)->collate('utf8_general_ci'); $table->string("host",100)->collate('utf8_general_ci'); $table->integer("port",5); $table->boolean("active")->default(false); $table->timestamps(); $table->softDeletes(); }); }

does the migration keeps any cache files?

@giwrgos right. It's all there in error message:

there can be only one auto column and it must be defined as a key

Yes.

id_platform_details int unsigned not null auto_increment primary key

port int not null auto_increment primary key

There are two. You must be missing something here.

I understand what the error message says but as you can see the port is only integer $table->integer("port",5);

I used to have unsigned() but I have removed it and still I'm getting this error message

@giwrgos it is strange. maybe you're running migration in a different environment and didn't deploy latest changes there? migrations only store information about which ones have already been applied. If unsure try to empty a database and recreate db structure from scratch with php artisan migrate:install && php artisan migrate.

@giwrgos also - this will hardly help, but still, try adding a ->default(...) or ->nullable() to integer field.

@constb i believe i found the problem. The problem was because i had $table->integer("port",5); the number 5 instead of this $table->integer("port");

i have same problem but i cant fix it this my problem https://laracasts.com/discuss/channels/laravel/1215-cannot-add-foreign-key-constraint

i had this problem to. just solved it. add ->unsigned();

Example: $table->integer('xxxxxxxx')->unsigned();

Well there are 3 things I did when I encountered this issue: Say I have two tables users and clients

1. I changed the timestamp of my users table to be earlier than clients table
2. Just to be sure everything is okay, I ran composer dump-autoload (since I use composer)
3. changed the type of the foreign key in my clients table to integer since the users id is also integer.

I would say my best bet was the first, the clients table is created before the users table, and was trying to assign a primary key of a users table that does not exist. Otherwise, the user_id column on the clients table as well should be unsigned and integer so to make things clear.

@bobbybouwmann perfect solution.

I recently had the same problem, but none of the suggested solutions worked. I just had to double check the key and the foreign key have the same format.

If in file 2015_06_25_153736_payment_systems.php you have

$table->bigIncrements('id_payment_system');

then in file 2015_06_25_153743_payment_currencies.php you should have

$table->bigInteger('payment_system_id_fn')->unsigned();

That worked for me.

@enea74 THANK YOU, your solution worked for me, i have been tried all, that is up, but only you resolved my problem, thank you

In my case, my users table was like MyISAM and the tables with relationship were going by default InnoDB, generating the error ,Cannot add foreign key constraint, MyISAM doesn't support foreign keys, check first of all which mysql engine is being used in tables. I solved my problems leaving the tables with the same innoDB engine. and adding $table->unsignedBigInteger('user_id');

!! MyISAM not support foreign key relationship

My laravel version 5.8.27
- Leave all tables as the same mysql innoDB engine
- $table->unsignedBigInteger('user_id'); 
- $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

I have similar trouble. And what I find: If you have table with key, that was made by id() method for examle

Schema::create('categories', function (Blueprint $table) {

		//...

        $table->id();

       // ....

    });

to make relation to it, you must use in dependent table column with "unsignedBigInteger" format:

		$table->unsignedBigInteger('id_category');

        $table->foreign('id_category')->references('id')->on('categories')->onDelete('cascade');

This works in my laravel 8 app. when I used "integer", or "bigInteger" it throws an error to me.

The same problem occurred to me, I spent several hours researching and trying to execute the corrections proposed in various forums, changing the attribute type, changing the search type, disabling constraints, nothing worked, so I started checking what could happen and came to an interesting solution.

When I created the attribute, I used ->unsigned() , following example:

Laravel 7x

Schema::table('users', function (Blueprint $table) {

         $table->bigInteger('company_id')->unsigned();
         $table->foreign('company_id')->references('id')->on('companies');
     });

In other words, I asked Laravel to create a company_id attribute, such as bigInteger, that did not allow the insertion of a value "zero or less than zero" ->unsigned(), not null (by default it already leaves the new attribute as not null) .

The "users" table already contained records, which caused Laravel to change the table, including a new attribute to it, but it ignored the issue of not accepting the insertion of values zero or less than zero (unsigned) and inserted it in all this table records the new attribute company_id with value 0 (zero).

Next, Laravel, through migrate, tried to change this new attribute (company_id) so that it was a foreign key, but it presented the exception:

General error: 1215 Cannot add foreign key constraint

The reason for this exception occurs because laravel, before making the foreign key change, checks the attribute rules in all records in the table and this attribute (company_id) being unsigned and having a zero value in the records, it understands that it will not be possible to proceed, which will give an error, displaying this exception and not executing the change.

In my case, I solved the problem with the following implementation: I went to the database, deleted the new attribute (which laravel had already created with a zero value).

When creating the attribute in the migration file, I asked Laravel to include the value 1 as the default (which is the lowest value allowed) and the code looked like this:

Laravel 7x

Schema::table('users', function (Blueprint $table) {

         $table->bigInteger('company_id')->unsigned()->default(1);
         $table->foreign('company_id')->references('id')->on('companies');
     });

I saved the file and ran migrate again.

Laravel understood what I requested, created the company_id attribute in the users table, and in the existing records it inserted the value 1 for all of them and then successfully executed the request to transform this attribute into a foreign key of the id attribute in the companies table .

Therefore, check if your database table already has records, if so, set the request to insert default value defaul() as 1 or another integer value greater than 1.

Regarding the issue of Laravel migrate adding an attribute with an incorrect value (zero) even with the unsigned restriction, it would be interesting for the Laravel developers to correct this, as in my opinion it is a failure.