Laravel Foreign Key Migration reference to String Issue

Question

I would like to know if there is any possibility to add string referenced foreign key in laravel migration. So, I have this codes on first file migration.

public function up()
{
    // roles table
    Schema::create('roles', function(Blueprint $table)
    {
        $table->engine = 'InnoDB';
        $table->increments('id');
        $table->integer('level')->unsigned();
        $table->string('title');
        $table->unique('level');
    });

    // account table
    Schema::create('account', function(Blueprint $table)
    {
        $table->increments('id');
        $table->integer('acc_no');
        $table->string('acc_username');
        $table->string('acc_password');
        $table->integer('acc_roles_level')->unsigned();
        $table->softDeletes();
        $table->timestamps();
        $table->unique('acc_username');
    });

then on the second migration file I have this code:

public function up()
{
    Schema::table('account', function(Blueprint $table)
    {
        $table->foreign('acc_roles_level')
              ->references('level')->on('roles')
              ->onUpdate('cascade')
              ->onDelete('set null');
    });
}

when migration run, it shows an error:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `account` add constraint account_acc_roles_level_foreign foreign key (`acc_roles_level`) references `roles` (`level`) on delete set null on update cascade) on update cascade)

I dont know why I can't make references to string column. meanwhile, I successfully run this code without error on mysql: alter table account add foreign key acc_roles_level references roles(level)

is there anyone has encountered the similar issues?

Should you not have both `acc_roles_level` and `level` in respective tables as unsigned ? — Noman Ur Rehman, Feb 01 '15 at 16:35
I think the problem is the `level` column is not the primary key in the `roles` table and you are adding it as a foreign key. Should not the foreign key be a primary key in its own table ? — Noman Ur Rehman, Feb 01 '15 at 17:09
@NomanUrRehman Not necessarily. A foreign key can reference any column that is unique, even if it is not a primary key. — Bogdan, Feb 01 '15 at 17:27
I also saw many examples of this implementation. however, it's mostly just referencing foreign key to integer type. is there any possibilities that I can reference it to string type column? — wrez, Feb 02 '15 at 02:32

score 1 · Answer 1 · answered Mar 02 '15 at 21:54

You have to change the migrations order

|-database/
|--migrations/
|---2015_02_02_141725_create_account_table.php
|---2015_03_01_234626_create_roles_table.php

Should be:

|-database/
|--migrations/
|---2015_01_01_234626_create_roles_table.php
|---2015_02_02_141725_create_account_table.php

Finally:

php artisan migrate

score 0 · Answer 2 · edited May 23 '17 at 12:26

After a lot of searching I changed my table def from

users table

public function up()
{
Schema::create('users', function (Blueprint $table) {
  $table->string('user_id');
  $table->string('short_name');
  $table->string('display_name');
  $table->string('username');
  $table->string('email');
}

to

public function up()
{
Schema::create('users', function (Blueprint $table) {
  $table->engine = 'InnoDB'; // <- add this
  $table->string('user_id')->primary(); // <- and this
  $table->string('short_name');
  $table->string('display_name');
  $table->string('username');
  $table->string('email');
}

bonus_user table

public function up()
{
Schema::create('bonus_user', function (Blueprint $table) {
  $table->engine = 'InnoDB';
  $table->string('bonus_id');
  $table->string('user_id');
});

Schema::table('bonus_user', function (Blueprint $table) {
  $table->foreign('bonus_id')->references('bonus_id')->on('bonuses')->onDelete('cascade');
  $table->foreign('user_id')->references('user_id')->on('users')->onDelete('cascade');
});

}

innoDB I got from here SO laravel foreign key migration ref string issue (see answer 2)

primary() I got from here enter link description here (though I didn't follow any other parts of the answer as I have to have my primary key as a string as it's supplied to me as a 24 char string!)

I think the 2 changes work together as it didn't work with just the InnoDB engine change alone.

The migration order may also be part of the solution but as my bonus_user table that references this foreign key was generated after the original users table migration the order of creation issue was not an issue for me!

After I added primary() to user_id column it worked first time!

Laravel Foreign Key Migration reference to String Issue

2 Answers2