php - SQLSTATE[HY000]: General error: 3780 Referencing column 'user_id' and referenced column 'id' in foreign key are incompatible

I??m doing migrations in Laravel and this error happens when I proceed with the command PHP artisan migrate:

In Connection.php line 664:

SQLSTATE[HY000]: General error: 3780 Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'almacen_movimientos_user_id_foreign' are incompatible. (SQL: alter table almacen_movimientos add constraint almacen_movimientos_user_id_foreign foreign key (user_id) references users (id
) on delete restrict)

In PDOStatement.php line 129:

SQLSTATE[HY000]: General error: 3780 Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'almacen_movimientos_user_id_foreign' are incompatible.

My migrations look like this:

almacen_movimientos table

public function up()
{
    Schema::create('almacen_movimientos', function (Blueprint $table) {
        $table->unsignedBigInteger('id');
        $table->integer('cliente_proveedor_id');
        $table->integer('empresa_id');
        $table->integer('user_id');
        $table->enum('tipo' , ['ENTRADA' , 'SALIDA' , 'REUBICACION' , 'TRASPASO' , 'DEVOLUCION' , 'MSRO' , 'ENTRADA POR TRASPASO' , 'SALIDA POR TRASPASO'])->nullable();
        $table->string('referencia' , 255)->nullable();
        $table->string('observaciones' , 255)->nullable();
        $table->timestamp('created_at');
        $table->timestamp('updated_at');
        $table->timestamp('deleted_at');
        $table->string('transportista' , 255)->nullable();
        $table->string('operador' , 255)->nullable();
        $table->string('procedencia' , 255)->nullable();
        $table->integer('almacen_id')->nullable();

        $table->foreign('cliente_proveedor_id')->references('id')->on('empresas')->onDelete('restrict');
        $table->foreign('empresa_id')->references('id')->on('empresas')->onDelete('restrict');
        $table->foreign('user_id')->references('id')->on('users')->onDelete('restrict');
        $table->foreign('almacen_id')->references('id')->on('almacenes')->onDelete('restrict');
    });
}

Users Table

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->unsignedBigInteger('id');
        $table->string('name' , 255);
        $table->string('apellido_paterno' , 115)->nullable();
        $table->string('apellido_materno' , 115)->nullable();
        $table->dateTime('fecha_nacimiento')->nullable();
        $table->string('telefono1' , 10)->nullable();
        $table->string('telefono2' , 10)->nullable();
        $table->string('calle' , 255)->nullable();
        $table->string('numero' , 45)->nullable();
        $table->string('colonia' , 255)->nullable();
        $table->string('codigo_postal' , 6)->nullable();
        $table->string('email' , 255)->unique();
        $table->string('user' , 20)->nullable()->unique();
        $table->string('password' , 255);
        $table->string('palabra_secreta' , 255);
        $table->string('remember_token' , 100)->nullable();
        $table->unsignedInteger('empresa_id')->nullable();
        $table->timestamp('created_at');
        $table->timestamp('updated_at');
        $table->timestamp('deleted_at');

        $table->foreign('empresa_id')->references('id')->on('empresas')->onDelete('restrict');
    });
}

Can somebopdy tell me what am I doing wrong? I cannot fix this.

Thank you.

Regards.

Solution:

In users table you have defined primary key with id as unsigned bigint and in almacen_movimientos table the referenced user_id is defined as int

change

$table->integer('user_id');

to

$table->unsignedBigInteger('user_id');

The structure and data type of PRIMARY KEY and FOREIGN KEY must be same

Solution:

You can also solve in a more elegant way changing in yout schema of almacen_movimientos you should change:

$table->integer('user_id');
$table->foreign('user_id')->references('id')->on('users')->onDelete('restrict');

with

$table->foreignId('user_id')->constrained()->onDelete('restrict');

Have a look at the documentation, you can do it from Laravel 7 on: https://laravel.com/docs/8.x/migrations#foreign-key-constraints

Solution:

$table->increments('id')  

use unsignedInteger in your foreign key

$table->id() 

            

use unsignedBigInteger in your foreign Key

Solution:

Make sure that foreign key datatype is the same as its referencing column

Solution:

Follow the dataType to resolve these issues.

public function up(){
Schema::create('posts', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('body');
    $table->unsignedBigInteger('user_id');
    $table->timestamps();
    $table->foreign('user_id')
        ->references('id')
        ->on('users')
        ->onDelete('cascade');
});
}

Noticed the local column ('id') was set to bigIncrements('id') and the foreign was set to Unsigned big integer.