1215 Can not add foreign key

Question

Larvel 5.6.3 PHP 7.2.10

I am getting this following error for php artisan migrate:fresh

General error: 1215 Cannot add foreign key constraint (SQL: alter table `videos` add constraint `videos_video_identified_by_foreign` foreign key (`video_identified_by`) references `users` (`id`))

user table migation file -> 2014_10_12_000000_create_users_table videos table migration file -> 2018_12_02_122553_create_videos_table

Normally it happens when parent table is not existing and we are using its column as foreign key in our table, but as it can be seen users table should be created first and then videos table will be created, then why I am getting this error.

users table

Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

videos

 Schema::create('videos', function (Blueprint $table) {
    $table->increments('video_id');
    $table->text('video_link');
    $table->text('video_description');
    $table->string('video_category');
    $table->string('video_language');
    $table->unsignedInteger('video_identified_by');
    $table->timestamps();            
});

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

Some time cache or sometime autoload files might result such issue, clear cache and run composer dumpautoload command. — Sagar Gautam, Dec 03 '18 at 07:20
@SagarGautam, thank you for your knid reply, I had run `php artisan config:cache` and `composer dump-autoload` before running this command still I am getting this error. — Prafulla Kumar Sahu, Dec 03 '18 at 07:23
is user.id a primary key? is this constraint "videos_video_identified_by_foreign" already present? I would advise you to thoroughly check your DB for any such things first. — Melroy Fernandes, Dec 03 '18 at 07:26
I always have this problem when I try to set a key to a column with and a column without the keyword `UNSIGNED` — Koen Hollander, Dec 03 '18 at 07:27
@MelroyFernandes Yes, users table is default laravel table and I have updatedmigration file code in question, please check it. — Prafulla Kumar Sahu, Dec 03 '18 at 07:29
@KoenHollander I tried that too previously, gives some other errors, so I am trying to use that. — Prafulla Kumar Sahu, Dec 03 '18 at 07:31
@JonasStaudenmeir I am using php artisan migrate:fresh, so all tables are empty. — Prafulla Kumar Sahu, Dec 03 '18 at 08:14

score 0 · Answer 1 · answered Dec 03 '18 at 07:35

0

hi try this is work fine for me

<?php
    $table->integer('video_identified_by')->unsigned();
    $table->foreign('video_identified_by')->references('id')->on('user')
          ->onUpdate('RESTRICT')->onDelete('CASCADE');      
?>

then php artisan migrate:refresh

answered Dec 03 '18 at 07:35

haffis asma

97
9

ah you make change in your id in table video try this – haffis asma Dec 03 '18 at 08:20
I dont think this works for you when you reference to the non existant user table. `on('users')` will be correct – Philipp Palmtag Dec 03 '18 at 08:45
i put the code try it or give me teamviewer id and password to fix it – haffis asma Dec 03 '18 at 08:45
ah yes i miss the s it s users try like that i edit the code – haffis asma Dec 03 '18 at 08:50

haffis asma · Answer 2 · 2018-12-03T08:50:10.313

ah you make change in your id in table video try this

     Schema::create('videos', function (Blueprint $table) {
        $table->increments('id');
        $table->text('video_link');
        $table->text('video_description');
        $table->string('video_category');
        $table->string('video_language');
        $table->integer('video_identified_by')->unsigned();
        $table->foreign('video_identified_by')->references('id')->on('users')
          ->onUpdate('RESTRICT')->onDelete('CASCADE');   
        $table->timestamps();            
    });

    }
 public function down()
    {
        Schema::dropIfExists('videos');
    }

score 0 · Answer 3 · answered Dec 04 '18 at 11:38

Add onDelete Cascade and change unsignedInteger to unsigned

Schema::create('videos', function (Blueprint $table) {
$table->increments('video_id');
$table->text('video_link');
$table->text('video_description');
$table->string('video_category');
$table->string('video_language');
$table->unsigned('video_identified_by');
$table->timestamps();            
});

Schema::table('videos', function($table) {
$table->foreign('video_identified_by')->references('id')->on('users')- 
>onDelete('cascade');
});

Thank you Shikha, but if you will see the comments, I have already tried that. — Prafulla Kumar Sahu, Dec 04 '18 at 14:58

1215 Can not add foreign key

3 Answers3