php Laravel 数据库架构，可空外国

Laravel Database Schema, Nullable Foreign

提问by nonsensecreativity

I've these two database tables:

我有这两个数据库表：

1. User Tables
2. Partner Tables

1. 用户表
2. 合作伙伴表

User Tableswill handle this kind of informations

用户表将处理此类信息

Schema::create('users', function (Blueprint $table) {
      $table->increments('id')->unique();
      $table->string('email')->unique();
      $table->string('username')->unique();
      $table->string('password', 60);
      $table->string('photo')->nullable();
      $table->integer('partner_id')->unsigned();
      $table->foreign('partner_id')->references('id')->on('partners');
      $table->rememberToken();
      $table->timestamps();
});

While Partner Tableswill contains all the user meta information such as first name and last name, etc.

虽然合作伙伴表将包含所有用户的元信息，如姓氏和名字等。

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

    /**
     * Identity Columns
     */
    $table->increments('id')->unique();
    $table->string('first_name');
    $table->string('middle_name')->nullable();
    $table->string('last_name')->nullable();
    $table->string('display_name')->nullable();
    $table->string('email')->unique()->nullable();
    $table->string('website')->nullable();
    $table->string('phone')->nullable();
    $table->string('mobile')->nullable();
    $table->string('fax')->nullable();
    $table->date('birthdate')->nullable();
    $table->longText('bio')->nullable();
    $table->string('lang')->nullable(); //Language

    /**
     * Address Columns
     */
    $table->text('street')->nullable();
    $table->text('street2')->nullable();
    $table->integer('country_id')->unsigned(); // foreign
    $table->foreign('country_id')->references('id')->on('countries');
    $table->integer('state_id')->unsigned();   // foreign
    $table->foreign('state_id')->references('id')->on('country_states');
    $table->string('city')->nullable();
    $table->string('district')->nullable();
    $table->string('area')->nullable();
    $table->string('zip')->nullable();
});

When a user is register to the site I only want few fields which are, username, email address, password, first nameand last name. These are only the required fields.

当用户注册到该站点时，我只需要几个字段username，即email address、password、first name和last name。这些只是必填字段。

So information in partner tables can be filled later after the user have finish registering to the site.

因此，合作伙伴表中的信息可以在用户完成网站注册后填写。

But due to the structure of foreign key, I cannot proceed any further because of this error :

但是由于外键的结构，由于此错误，我无法继续进行：

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mytable`.`tbl_partners`, CONSTRAINT `partners_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `tbl_countries` (`id`)) (SQL: insert into `tbl_partners` (`first_name`, `last_name`, `display_name`, `email`, `updated_at`, `created_at`) values (Hyman, Wilson, admin, [email protected], 2016-06-09 19:41:18, 2016-06-09 19:41:18))

I know this is cause by the countries table which is required by the partner table.
My question is : is there a work around so I can fill the country or any other non required data on partner table but keep the foreign table schema for countries, states, etc.

我知道这是由合作伙伴表所需的国家/地区表引起的。
我的问题是：是否有解决方法，以便我可以在合作伙伴表上填写国家/地区或任何其他不需要的数据，但保留国家、州等的外部表架构。

回答by rcx1

Set the country_idand the state_idnullable, like so.

像这样设置country_id和 可以为state_id空。

$table->integer('country_id')->nullable()->unsigned();

$table->integer('state_id')->nullable()->unsigned();

回答by moghwan

For laravel 7.x to create a nullable foreign key use simply:

要让 laravel 7.x 创建一个可为空的外键，只需使用：

$table->foreignId('country_id')->nullable()->constrained();

$table->foreignId('state_id')->nullable()->constrained();

REMEMBER: nullableshould be beforeconstrainedotherwise the nullable will not be affected.

记住：nullable应该在约束之前，否则 nullable 不会受到影响。

回答by bobylito

With the latest version of Laravel, you can use the nullablemethod in conjunction of foreignKey:

使用最新版本的 Laravel，您可以nullable结合使用该方法foreignKey：

$table
      ->foreignId('other_table_id')
      ->nullable() // here
      ->references('id')
      ->on('other_table');

回答by Payam Khaninejad

For Laravel 7.x I use this way:

对于 Laravel 7.x，我使用这种方式：

$table->bigInteger('word_type_id')->nullable()->unsigned();
$table->index('word_type_id')->nullable();
$table->foreign('word_type_id')->nullable()->references('id')->on('word_types')->onDelete('cascade');