Laravel migration create custom foreign key-CodePudding

I am creating migration in laravel, where at some point I need to create custom foreign key name in database. I have searched this solution but there is nothing similar to my problem.

I want to add columns for names table that contain birth_city_id, birth_state_id and birth_country_id. Apart from this I also want to add death_city_id, death_state_id, death_counrty_id.

Error:

Migrating: 2021_11_21_130238_create_names_table

   Illuminate\Database\QueryException 

  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 (SQL: alter table `names` add constraint `birth_city_id` foreign key (`city_id`) references `cities` ())

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:703
    699▕         // If an exception occurs when attempting to run a query, we'll format the error
    700▕         // message to include the bindings with SQL, which will make this exception a
    701▕         // lot more helpful to the developer instead of just the database's errors.
    702▕         catch (Exception $e) {
  ➜ 703▕             throw new QueryException(
    704▕                 $query, $this->prepareBindings($bindings), $e
    705▕             );
    706▕         }
    707▕     }

       9 vendor frames 
  10  database/migrations/2021_11_21_130238_create_names_table.php:37
      Illuminate\Support\Facades\Facade::__callStatic()

       21 vendor frames 
  32  artisan:37
      Illuminate\Foundation\Console\Kernel::handle()

Below is the related article but that does't work. https://www.itsolutionstuff.com/post/laravel-migration-custom-foreign-key-name-exampleexample.html

Schema::create('names', function (Blueprint $table) {
            $table->id();
            $table->string('fname')->nullable();
            $table->string('mname')->nullable();
            $table->string('lname')->nullable();
            $table->string('image')->nullable();
            $table->string('nickname')->nullable();
            $table->string('height')->nullable();
            $table->string('gender',15)->nullable();
            $table->date('dob')->comment('Date of Birth')->nullable();
            $table->date('dod')->comment('Date of Death')->nullable();   
            $table->unsignedBigInteger('city_id');
            $table->unsignedBigInteger('state_id');
            $table->unsignedBigInteger('country_id');              
            $table->foreign('city_id','birth_city_id')->nullable()->refrences('id')->on('cities');
            $table->foreign('state_id','birth_state_id')->nullable()->refrences('id')->on('states');
            $table->foreign('country_id','birth_country_id')->nullable()->refrences('id')->on('countries');            
            $table->text('mini_bio')->nullable();
            $table->boolean('is_active')->nullable();
            $table->softDeletes();
            $table->timestamps();
        });

Table cities, states, countries are already exist in database.

Schema::create('cities', function (Blueprint $table) {
        $table->id();
        $table->string('name')->nullable();
        $table->string('code',10)->nullable();
        $table->foreignId('state_id')->constrained();
        $table->softDeletes();
        $table->timestamps();
});

CodePudding user response：

the method foreign take string|array as $columns as its first argument, then a string as the index name ...

anyway, if you want to make multi references to another table, you should have multi-column for that:

 $table->unsignedBigInteger('birth_city_id')->nullable();;
 $table->unsignedBigInteger('death_city_id')->nullable();;
 $table->foreign('birth_city_id')->references('id')->on('cities');
 $table->foreign('death_city_id')->references('id')->on('cities');

by the way, I found a typo in your code, it references not refrences