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

Question

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.

Answer 1

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

Answer 2

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