Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel Database Schema, Nullable Foreign

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, [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.

like image 958
nonsensecreativity Avatar asked Jun 09 '16 19:06

nonsensecreativity


Video Answer


2 Answers

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.

like image 183
moghwan Avatar answered Oct 19 '22 16:10

moghwan


Set the country_id and the state_id nullable, like so.

$table->integer('country_id')->nullable()->unsigned();  $table->integer('state_id')->nullable()->unsigned(); 
like image 24
rcx1 Avatar answered Oct 19 '22 18:10

rcx1