phpmyadmin Question posted in
The official documentation can be found here.

What is the correct foreign key constraint syntax for a database migration in Laravel 8.X Eloquent? – Phpmyadmin

I am a Laravel and Eloquent noob.

I’m trying to do a simple foreign key in a "checklist items" table that takes the primary ID from the "users" table as a foreign key.

Here are my two database migrations:

users:

<?php

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

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

items:

<?php

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class CreateItemsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('items', function (Blueprint $table) {
            $table->id();
            $table->integer('user_id')->unsigned();
            $table->string('itemName');
            $table->string('itemDescription');
            $table->timestamps();
        });
        
        Schema::table('items', function($table) {
            $table->foreign('user_id')->references('id')->on('users');
        });
        
    }

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

attempting to run php artisan migrate yields the following error:

Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (0.49 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.59 seconds)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (0.33 seconds)
Migrating: 2021_05_04_085648_create_items_table

   IlluminateDatabaseQueryException

  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `items` add constraint `items_user_id_foreign` foreign key (`user_id`) references `users` (`id`))

  at D:ApplicationslaragonwwwToDoAppvendorlaravelframeworksrcIlluminateDatabaseConnection.php:671
    667|         // If an exception occurs when attempting to run a query, we'll format the error
    668|         // message to include the bindings with SQL, which will make this exception a
    669|         // lot more helpful to the developer instead of just the database's errors.
    670|         catch (Exception $e) {
  > 671|             throw new QueryException(
    672|                 $query, $this->prepareBindings($bindings), $e
    673|             );
    674|         }
    675|

  1   D:ApplicationslaragonwwwToDoAppvendorlaravelframeworksrcIlluminateDatabaseConnection.php:464
      PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")

  2   D:ApplicationslaragonwwwToDoAppvendorlaravelframeworksrcIlluminateDatabaseConnection.php:464
      PDOStatement::execute()

It should be noted that I can achieve what I need fine in the PHPMyAdmin GUI directly, or using basic PHP PDOs, but I don’t understand Eloquent and need to understand what I am doing wrong.

I have a feeling that it’s to do with a mismatch in the attribute definitions between the two table migrations, but the few things I tried caused alternate errors.

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    columns must be of the same type and sign (signed or unsigned)

    $table->integer('user_id')->unsigned(); should be $table->bigInteger('user_id')->unsigned();


  2. 0

    Replace your items migration with the following code:

    <?php

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class CreateItemsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('items', function (Blueprint $table) {
            $table->id();
            $table->integer('user_id')->unsigned();
            $table->string('itemName');
            $table->string('itemDescription');
            $table->timestamps();

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

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('items');
    }
}
    Login or Signup to reply.
  3. 0

    Just make a new Migration file for only the foreign key (docs). something like

    php artisan make:migration add_columns_to_items_table

    Why use different file for same table? Because we make the table first before we make the foreignKey right? 🙂

    Then inside your migration file on up() function:

    Schema::table('items', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
});

    Yah, its shorter and better for development purpose

    Login or Signup to reply.
  4. 0

    This happens when you create the migration for the many before the one part of one-to-many relationship. In your case I guess you created migration for items before users. Simply rename your migration files according to your relationships. Also please note that your relationship columns must be of the same type. In your case, you are referencing an integer of 4-byte to bigInteger of 8-byte. (id columns is an unsigned 8-byte integer).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search