Please resolve migration error in laravel.

[GoldenDeveloper613]

Body

I tried to disable autoincreament prop of users primary key.
By searching github community i found it and changed my migration as follows.

$table->integer('id')->unsigned();
$table->primary('id');

But after changing user migration, following error occured.

SQLSTATE[HY000]: General error: 1005 Can't create table `laravel1`.`clients` (errno: 150 "Foreign 
key constraint is incorrectly formed") (Connection: mysql, SQL: alter table `clients` add constraint `clients_user_id_foreign` foreign key (`user_id`) references `users` (`id`))

  at vendor\laravel\framework\src\Illuminate\Database\Connection.php:813
    809▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e        
    810▕                 );
    811▕             }
    812▕
  ➜ 813▕             throw new QueryException(
    814▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    815▕             );
    816▕         }
    817▕     }

  1   vendor\laravel\framework\src\Illuminate\Database\Connection.php:571
      PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `laravel1`.`clients` (errno: 150 "Foreign key constraint is incorrectly formed")")

  2   vendor\laravel\framework\src\Illuminate\Database\Connection.php:571
      PDOStatement::execute()

Following is clients migration.

    public function up(): void
    {
        Schema::create('clients', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->unsigned()->comment('this is a broker id');
            $table->string('email', 191)->nullable();
            $table->string('phone', 20)->nullable();
            $table->unsignedBigInteger('model_id')->nullable();

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

Please give me answer how to solve this issue.

Guidelines

• I have read and understood this category's guidelines before making this post.

[rohispace90]

Hello dear;
The error you encountered is due to a mismatch between the data types of the primary key id in the users table and the foreign key user_id in the clients table. When you changed the id column in the users table from an auto-incrementing integer to a simple unsigned integer, you unintentionally caused this mismatch.

Here's how to resolve this issue:
1- Ensure that both the id in the users table and the user_id in the clients table have matching data types.
2- Drop and recreate the foreign key constraints after making these changes.

Here’s a step-by-step solution:
Step 1: Modify the users table migration
Make sure your users table migration uses the unsignedBigInteger for the id column, as it matches the default type for Laravel's foreignId:

Schema::create('users', function (Blueprint $table) {
    $table->unsignedBigInteger('id')->primary();
    // Other columns...
});

Step 2: Modify the clients table migration
Ensure that the user_id column in the clients table is also of type unsignedBigInteger to match the id column in the users table:

public function up(): void
{
    Schema::create('clients', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('user_id')->comment('this is a broker id');
        $table->string('email', 191)->nullable();
        $table->string('phone', 20)->nullable();
        $table->unsignedBigInteger('model_id')->nullable();

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

Step 3: Update existing migrations (if necessary)
If you already have existing tables, you need to drop and recreate the foreign key constraints. You can do this by modifying existing migrations or creating new migrations to alter the tables.
Here's an example of how you can create a new migration to modify the user_id column in the clients table:
php artisan make:migration update_user_id_in_clients_table --table=clients

Then, in the newly created migration file:

public function up(): void
{
    Schema::table('clients', function (Blueprint $table) {
        $table->dropForeign(['user_id']);
        $table->unsignedBigInteger('user_id')->change();
        $table->foreign('user_id')->references('id')->on('users');
    });
}

public function down(): void
{
    Schema::table('clients', function (Blueprint $table) {
        $table->dropForeign(['user_id']);
        $table->integer('user_id')->unsigned()->change();
        $table->foreign('user_id')->references('id')->on('users');
    });
}

Step 4: Run the migrations
After making the changes, run the migrations to apply them:
php artisan migrate

By ensuring that the id column in the users table and the user_id column in the clients table have matching data types, you should be able to resolve the foreign key constraint issue.

Be lucky!

[fdev0307]

Hi, GoldenDeveloper.

Your error occurred because data type of users.id and clients.user_id are different.
In laravel default type of primary key is bigInteger.

So you have to change type of custom primary key to bigInteger.

Here is code.

$table->bigInteger('id')->unsigned();
$table->primary('id');

or

$table->unsignedBigInteger('id')->primary();

[GoldenDeveloper613]

Thanks for your answer.