Laravel foreign key constraint is incorrectly formed I have searched and cannot find the answer

Question

class CreateMediaTable extends Migration
{
    public function up()
    {
        Schema::create('media', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedBigInteger('id_users');
            $table->unsignedBigInteger('id_posts');
            $table->char('type', 1); //P: photo or V: video
            $table->string('file');
            $table->timestamps();

            $table->foreign('id_posts')->references('id')->on('posts');
            $table->foreign('id_users')->references('id')->on('users');
        });
    }

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

and my createprofilemigration

/**
 * @author Alex Madsen
 * 
 * @date November 6, 2018
 */ 

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

class CreateUserProfilesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('user_profiles', function (Blueprint $table) {
            $table->unsignedInteger('id_users')->unique();
            $table->string('handle')->unique(); 
            $table->string('icon')->default('https://i.redd.it/130am13nj6201.png'); 
            $table->string('profile_image')->nullable(); 
            $table->string('description')->nullable(); 
            $table->timestamps();
        });
    }

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

Keeps throwing these errors, What am I missing? Mind you I am new to this, Trying to learn with youtube and stackoverflow on my off time. Not sure which way to go. I have looked on the forums and tried $table->foreign('id_posts')->references('id')->on('posts'); But it didn't fix the issue.

Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1005 Can't create table `ci`.`media` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter t
able `media` add constraint `media_id_posts_foreign` foreign key (`id_posts`) references `posts` (`id`))

  at C:\xampp6\htdocs\lol\simple_social_network_laravel\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|         }

class CreatePostsTable extends Migration
{
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedBigInteger('id_users');
            $table->unsignedInteger('subreddit_id');
            $table->text('description');
            $table->string('title');
            $table->text('content');
            $table->unsignedInteger('id_posts');
            $table->timestamps();

            $table->foreign('id_users')->references('id')->on('users');
            $table->foreign('subreddit_id')->references('id')->on('id_posts');
        });
    }

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

in your `media` migration edit your `posts_id` to `unsignedInteger` to be like this `$table->unsignedInteger('id_posts');` — Joseph, Jan 05 '20 at 13:27

score 2 · Answer 1 · answered Jan 05 '20 at 13:25

2

When using foreign keys types should be exactly the same.

You create:

$table->unsignedBigInteger('id_posts');

so it's unsigned big integer, but probably in posts table instead of bigIncrements you use just increments for id column and that's why you are getting this error.

So quite possible instead of:

$table->unsignedBigInteger('id_posts');

you should use

$table->unsignedInteger('id_posts');

or as alternative solution use

$table->bigIncrements('id');

in posts migration

answered Jan 05 '20 at 13:25

Marcin Nabiałek

109,655
42
258
291

@Insomniakinn Yes, so in answer you have solution what you should change – Marcin Nabiałek Jan 05 '20 at 13:51
`$table->increments('id'); $table->unsignedInteger('id_users'); $table->unsignedInteger('id_posts'); $table->char('type', 1); //P: photo or V: video $table->string('file'); $table->timestamps(); $table->foreign('id_posts')->references('id')->on('posts'); $table->foreign('id_users')->references('id')->on('users');` It's still throwing the same error. Granted I am new to coding, learning on my days off so I am sure user error. – Insomniakinn Jan 05 '20 at 13:52
@Insomniakinn Are you sure error is the same? You should make only change in single file, not in both and it should work – Marcin Nabiałek Jan 05 '20 at 14:04
I went ahead and uploaded code to git, Not sure if having the big picture helps or not. https://github.com/Wildindfw/mylaraveltest – Insomniakinn Jan 05 '20 at 14:11

score 0 · Answer 2 · answered Jan 05 '20 at 13:32

In your posts migration, you made your id with increments that is mean that it takes

unsigned integer auto_increment

but in your migration of media file, you create a posts_id with unsignedBigInteger

so there's two way to fix that choose only one

edit your id in posts migration to be bigincrements
edit your posts_id in 'media' migration to unsignedIntegere

Laravel foreign key constraint is incorrectly formed I have searched and cannot find the answer

2 Answers2