使用外键添加laravel迁移

Question

我创建了一个名为用户的表。有一些表被称为公司、名称、部门。我想添加company_id，designation_id，department_id列到用户表作为外键。

我试过了，但没用

public function up()
{
    Schema::table('users', function (Blueprint $table) {

        $table->integer('department_id');
        $table->integer('company_id');
        $table->integer('designation_id');

        $table->foreign('department_id')->references('id')->on('departments')->onDelete('restrict')->onUpdate('restrict');
        $table->foreign('company_id')->references('id')->on('companies')->onDelete('restrict')->onUpdate('restrict');
        $table->foreign('designation_id')->references('id')->on('designations')->onDelete('restrict')->onUpdate('restrict');



    });
}


public function down()
{
    Schema::table('users', function (Blueprint $table) {
        $table->dropColumn(['department_id','company_id','designation_id']);
    });
}

当我迁移迁移时，它会显示此错误。

Illumb\Database\QueryException:SQLSTATE[HY000]：一般错误：1005无法创建表lanwadbusers（错误号：150“外键约束格式不正确”）（SQL:alter tableusers添加约束users\u department\u id\u Foreign外键（department\u id）参考部门（id）删除限制更新限制）

名称如下：，

public function up()
    {
        Schema::create('designations', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestampsTz();
        });
    }

部门迁移如下，

public function up()
    {
        Schema::create('departments', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->integer('company_id');
            $table->timestampsTz();
        });
    }
    ```

Answer 1

使用以下命令：

    $table->integer('department_id')->unsigned()->nullable();
    $table->integer('company_id')->unsigned()->nullable();
    $table->integer('designation_id')->unsigned()->nullable();
    $table->foreign('department_id')->references('id')->on('departments')->onDelete('cascade')->onUpdate('cascade');
    $table->foreign('company_id')->references('id')->on('departments')->onDelete('cascade')->onUpdate('cascade');
    $table->foreign('designation_id')->references('id')->on('departments')->onDelete('cascade')->onUpdate('cascade');

Answer 2

要建立关系，外键字段应为索引。这里有三列要用作外键'company\u id'，'department\u id'和'designation\u id'。在Laravel迁移中，可以使用unsigned（）函数对它们进行索引。

例子：

$table->integer('department_id')->unsigned();
$table->integer('company_id')->unsigned();
$table->integer('designation_id')->unsigned();

还有另一个名为unsignedInteger（）的函数，通过该函数可以使列同时为整数和无符号。

$table->unsignedInteger('department_id');
$table->unsignedInteger('company_id');
$table->unsignedInteger('designation_id');