Laravel database schema, nullable foreign keys
P粉805107717
P粉805107717 2023-10-17 14:43:49
0
2
700

I have these two database tables:

  1. user table
  2. Partner List

User tablewill handle such information

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

And Partner table will contain all 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 registers to the website, I only need a few fields, namely Username, Email Address, Password, Name and surname. These are required fields only.

Therefore, the information in the partner table can be filled in after the user completes the website registration.

But due to the structure of the foreign keys, I cannot proceed due to 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 (Jack, Wilson, admin, admin@example.com, 2016-06-09 19:41:18, 2016-06-09 19:41:18))

I know this is caused by the country table required by the partners table.

My question is: Is there a workaround so that I can fill in the partners table with country or any other non-required data, but keep the external table schema for country, state, etc.

P粉805107717
P粉805107717

reply all(2)
P粉135799949

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

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

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

Remember: Nullability should be preceded by constrained , otherwise nullability will not be affected.

P粉638343995

Set country_id and state_id to be nullable as shown below.

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

$table->integer('state_id')->nullable()->unsigned();
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template