Laravel Database Schema, Nullable Foreign

PhpMysqlDatabaseLaravel

Php Problem Overview


I've these two database tables:

  1. User Tables
  2. Partner Tables

User Tables will 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 Tables will 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 name and last name. These are only the required fields.

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 (Jack, Wilson, admin, admin@example.com, 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.

Php Solutions


Solution 1 - Php

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

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

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

REMEMBER: nullable should be before constrained otherwise the nullable will not be affected.

Solution 2 - Php

Set the country_id and the state_id nullable, like so.

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

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

Solution 3 - Php

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

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

Solution 4 - Php

For Laravel 7.x I use this way:

$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');

Solution 5 - Php

laravel 8

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionnonsensecreativityView Question on Stackoverflow
Solution 1 - PhpmoghwanView Answer on Stackoverflow
Solution 2 - Phprcx1View Answer on Stackoverflow
Solution 3 - PhpbobylitoView Answer on Stackoverflow
Solution 4 - PhpPayam KhaninejadView Answer on Stackoverflow
Solution 5 - Phpali hassanView Answer on Stackoverflow