Laravel 7，SQLSTATE [23000]：完整性约束违规：

Question

我正在使用 MySQL 8.0 在 PHP 7.4 上运行 Laravel 7。我有三个表，User和Company，Department以及它们各自的模型和工厂。我创建了一个要添加关系的测试：// MyTest.php$user = factory(User::class)->create();$company = factory(Company::class)->make();$company->user()->associate($user);$company->create(); // it fails here because of NOT NULL constraint, companies.user_id$department = factory(Department::class)->make();$department->company()->associate($company);$department->create();我收到以下错误：Integrity constraint violation: 19 NOT NULL constraint failed: companies.user_id (SQL: insert into "companies" ("updated_at", "created_at") values (2020-03-10 07:27:51, 2020-03-10 07:27:51))我的表模式定义如下：// usersSchema::create('users', function (Blueprint $table) {    $table->id();    $table->string('name');    $table->string('email')->unique();    $table->timestamp('email_verified_at')->nullable();    $table->string('phone');    $table->integer('user_type');    $table->string('password');    $table->rememberToken();    $table->timestamps();});// companiesSchema::create('companies', function (Blueprint $table) {    $table->id();    $table->foreignId('user_id')->constrained()->onDelete('cascade');    $table->string('name');    $table->string('contact_email');    $table->string('contact_phone');    $table->timestamps();});// departmentsSchema::create('departments', function (Blueprint $table) {    $table->id();    $table->foreignId('company_id')->constrained()->onDelete('cascade');    $table->string('name');    $table->string('contact_email');    $table->string('contact_phone');    $table->timestamps();});->nullable()我的理解是 SQL 表中不应该有 NULL 值，这就是为什么我在迁移中刻意避免使用的原因。特别是对于像这样的外键。

Answer 1

乍一看，您的表结构的一些问题非常明显。

• 您似乎正在尝试向表中添加一user_id列companies。假设您的公司有不止一名员工，这不是一个好主意。

• 如果要使用NOT NULL列，最好为每个列定义一个默认值。

所以我们可以从编写类似这样的迁移开始，包括公司/用户和部门/用户关系的数据透视表：

// companies

Schema::create('companies', function (Blueprint $table) {

    $table->id();

    $table->string('name');

    $table->string('contact_email')->default('');

    $table->string('contact_phone')->default('');

    $table->timestamps();

});

// departments

Schema::create('departments', function (Blueprint $table) {

    $table->id();

    $table->foreignId('company_id')->constrained()->onDelete('cascade');

    $table->string('name');

    $table->string('contact_email')->default('');

    $table->string('contact_phone')->default('');

    $table->timestamps();

});

// users

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

    $table->id();

    $table->string('email')->unique();

    $table->timestamp('email_verified_at')->nullable();

    $table->string('name')->default('');

    $table->string('phone')->default('');

    $table->integer('user_type')->default(0);

    $table->string('password');

    $table->rememberToken();

    $table->timestamps();

});

Schema::create('company_user', function (Blueprint $table) {

    $table->id();

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

    $table->foreignId('company_id')->constrained()->onDelete('cascade');

});

Schema::create('department_user', function (Blueprint $table) {

    $table->id();

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

    $table->foreignId('department_id')->constrained()->onDelete('cascade');

});

现在我们有了表之间的链接。部门是公司的一部分；一个用户可以是多个部门和/或多个公司的一部分。这导致以下关系：

class User extends Model {

    // many-to-many

    public function companies() {

        return $this->belongsToMany(App\Company::class);

    }

    // many-to-many

    public function departments() {

        return $this->belongsToMany(App\Department::class);

    }

}

class Company extends Model {

    public function departments() {

        // one-to-many

        return $this->hasMany(App\Department::class);

    }

    public function users() {

        // many-to-many

        return $this->belongsToMany(App\User::class);

    }

}

class Department extends Model {

    public function company() {

        // one-to-many (inverse)

        return $this->belongsTo(App\Company::class);

    }

    public function users() {

        // many-to-many

        return $this->belongsToMany(App\User::class);

    }

}

现在这样的代码应该可以工作了：

$user = factory(User::class)->create();

$company = factory(Company::class)->create();

$user->companies()->attach($company);

$company->departments()->create([

    'name' => 'Department 1',

    'contact_email' => '[e-mail]',

    'contact_phone' => '123456789',

]);

具体来说，该attach方法用于根据您的原始表格布局更新您似乎没有定义的多对多关系。