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))

我的表模式定义如下：

// users

Schema::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();

});

// companies

Schema::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();

});

// departments

Schema::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列，最好为每个列定义一个默认值。所以我们可以从编写类似这样的迁移开始，包括公司/用户和部门/用户关系的数据透视表：// companiesSchema::create('companies', function (Blueprint $table) {    $table->id();    $table->string('name');    $table->string('contact_email')->default('');    $table->string('contact_phone')->default('');    $table->timestamps();});// departmentsSchema::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();});// usersSchema::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方法用于根据您的原始表格布局更新您似乎没有定义的多对多关系。