Cannot add foreign key constraint

@jasonb uhmm, may be...

Schema::create("yourTable", function(Blueprint $table) {
    $table->engine = "InnoDB";
}

same error: [Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL : alter table article_tags add constraint article_tags_article_id_foreign foreign key (article_id) references articles (id) on delete cascade)

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

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTagsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('tags', function(Blueprint $table)
        {
            $table->engine = "InnoDB";
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });

        Schema::create('article_tags', function(Blueprint $table)
        {
            $table->engine = "InnoDB";
            $table->integer('article_id')->unasigned()->index();
            $table->foreign('article_id')->references('id')->on('articles')->onDelete('cascade');

            $table->integer('tag_id')->unasigned()->index();
            $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');
            $table->timestamps();
        });



    }



    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('tags');
        Schema::drop('article_tag');
    }

}

@jasonb uhmm, and your articles table?

@Ricardo

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8_unicode_ci NOT NULL,
  `link` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category_id` int(11) NOT NULL,
  `published_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `published_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `articles_user_id_foreign` (`user_id`),
  CONSTRAINT `articles_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

This is driving me nuts! =) I just don't see why the foreign key wont work. I even tried this inside of Sequel Pro and it doesn't work.

@jasonb what is the order of creating tables? Are you creating articles table before article_tags?

Can you paste structure (from SequelPro) of those 3 tables?

Yes you have to make right order and you should just Refactor > Rename and rename file (change date or trailing number) to push some file up/down migration sorts files by name.

I think I figured it out. In Sql Pro For the ID's I unchecked Unsigned. I was then able to run this from the Sequel Pro Gui.. I am not sure how the migration can fix this. I Guess this is an issue with MySQL?

CREATE TABLE `article_tag` (
  `article_id` int(11) NOT NULL DEFAULT '5',
  `tag_id` int(11) NOT NULL DEFAULT '5',
  PRIMARY KEY (`article_id`),
  KEY `tag_id` (`tag_id`),
  CONSTRAINT `article_tag_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`tag_id`) ON DELETE CASCADE,
  CONSTRAINT `article_tag_ibfk_2` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

talking about MySQL I have git the wall with my head the other day with this setting:

| sql_mode                 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

You may develop app on server where sql_mode "" and deploy to this animal and then everything brakes apart... columns that are nullable or not declared as nullable, if you have 'too much data that was trimmed before' now will result in error and stuff like that.

You want unsigned, not unasigned in your migration. You don't need to manually add the index - adding a foreign key will handle that for you.

Make sure you create your foreign keys before you insert any data. Best practice is to make the foreign key field nullable, as NULL is the only value you will be able to use, other than a foreign value.

So my question is how did Jeff do this and not get any errors? https://laracasts.com/series/laravel-5-fundamentals/episodes/21 Start 6.00 into the video.

The migration I did was identical. Very frustrated. =)

But you have a typo - as @deringer says 'unasigned' is not valid. I'm surprised this is not the line throwing the error, but it may be that the foreign key constraint is failing because you did not specify 'unsigned' for the column?

That was most likely it.

I had this same problem and did not use unassigned() or unsigned() for that matter.

Here's the solution that worked for me. Seems like a buggy bug.

Add the constraint in a second step:

` public function up() { Schema::create('priorities', function($table) { $table->increments('id', true); $table->integer('user_id')->unsigned(); $table->string('priority_name'); $table->smallInteger('rank'); $table->text('class'); $table->timestamps('timecreated'); });

Schema::table('priorities', function($table) { $table->foreign('user_id')->references('id')->on('users'); });

} `

Just in case anyone has this problem. In my case, I had to drop and create the database again. Then I could migrate without any problems.

Guys is very important the order of migrations you can't create first the table that is setting the foreign key for a table that doesn't exist yet, a quickly solution is to rename the migration file changing the datetime stamp, example:

2016_06_28_185212_create_posts_table.php --> migration that is trying to set the foreign key, but at this point the users table doesn't exist! 2016_06_28_185011_create_users_table.php

So rename datetime segment in order that users table is created first:

2016_06_28_185011_create_posts_table.php --> Now this is created first 2016_06_28_185212_create_users_table.php --> Then you can set the foreign key!

I need glasses jejeje, I had the same problem, but now I found the problema, I wrote "unsígned();" instead unsigned(); now it is working fine!

I have the same problem but after i read this and i reorder file names like you say Thanks bro => "@NexusPlex" every think work fine now :)

@michaeldyrynda , I know you wrote this 2 years ago , but i am suprised by this line:

Make sure you create your foreign keys before you insert any data. Best >practice is to make the foreign key field nullable, as NULL is the only value >you will be able to use, other than a foreign value.

Is this really the case? I never knew this and i'm pretty suprised. Can you tell me what advantages this gives?

@Chrizzmeister if you had a user_id column in a table and later decided to add a foreign key constraint to it, any records that either had no value or the value of a non-existent foreign id i.e. a deleted user would cause adding of the foreign key constraint to fail.

Thanks @NexusPlex! Worked just fine!

@BONDACOM - just tried this today and it worked for me