SQLSTATE[HY000]: 常规错误: 3780 引用列“user_id”和外键中引用的列“id”不兼容

2022-08-30 22:51:27

我正在Laravel中进行迁移,当我继续执行以下命令时会发生此错误:PHP artisan migrate

连接.php 664行:

SQLSTATE[HY000]:常规错误:3780 引用外键约束“almacen_movimientos_user_id_foreign”中的引用列“user_id”和引用的列“id”不兼容。(SQL:更改表添加约束外键 () 引用 (
) 删除限制)almacen_movimientosalmacen_movimientos_user_id_foreignuser_idusersid

在 PDOStatement 中.php第 129 行:

SQLSTATE[HY000]:常规错误:3780 引用外键约束“almacen_movimientos_user_id_foreign”中的引用列“user_id”和引用的列“id”不兼容。

我的迁移如下所示:

almacen_movimientos表

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');
    });
}

用户表

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');
    });
}

有人能告诉我我做错了什么吗?我无法解决此问题。

谢谢。

问候。


答案 1

在表中,您已将 id 定义为无符号 bigint 的主键,在表中,引用的主键定义为 intusersalmacen_movimientosuser_id

改变

$table->integer('user_id');

$table->unsignedBigInteger('user_id');

主键和外键的结构和数据类型必须相同


答案 2

您还可以以更优雅的方式解决您应该更改的架构的变化:almacen_movimientos

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

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

看看文档,你可以从Laravel 7开始:https://laravel.com/docs/8.x/migrations#foreign-key-constraints


推荐