How to create a self-reference relationship (foreign key) in a Laravel migration?

Question

I am starting with a self-learning project built on top of Laravel 5.2 and I have found my first issue: self-reference in a migration.

This is how the file 2016_08_02_024942_create_navigation_table.php looks like (I have remove the comments for not make the post too long):

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateNavigationTable extends Migration
{
    public function up()
    {
        Schema::create('navigation', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id')->unsigned();
            $table->integer('position')->unsigned();
            $table->string('title');
            $table->string('slug');
            $table->string('permissions')->nullable();
            $table->timestamps();
            $table->softDeletes();
        });
    }

    public function down()
    {
        Schema::drop('articles');
    }
}

And then because I have read several posts here like this, this, this and many more I make another file with just the relation named as 2016_08_02_030158_add_parent_to_navigation_table.php with the following code:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddParentToNavigationTable extends Migration
{
    public function up()
    {
        Schema::table('navigation', function (Blueprint $table) {
            $table->foreign('parent_id')->references('id')->on('navigation')->onUpdate('cascade')->onDelete('cascade');
        });
    }

    public function down()
    {
        Schema::drop('articles');
    }
}

But when I run the command php artisan migrate I got the following error and I am not sure what I am doing wrong:

[Illuminate\Database\QueryException] SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'navigation' already exists (SQL: create table navigation (id int unsigned not null auto_increment primary key, position int unsigned not null, title varc har(255) not null, slug varchar(255) not null, permissions varchar(255) null, created_at timestamp null, updated_at timestamp null, deleted_at timestamp null) default character set utf8 collate utf8_unicode_ci engin e = InnoDB)

[PDOException] SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'navigation' already exists

Can any give me some advice? What I am doing wrong? I have see this package but I am not sure if it will solve my issue.

Also you don't need add ```unsigned()``` for ```increments()``` but default it is unsigned (Tip) — devnull, Aug 02 '16 at 03:23
@xdevnull I've run the command `php artisan migrate:rollback` and it clear the DB — ReynierPM, Aug 02 '16 at 03:23
please make sure it was removed. ```rollback``` is not enough prove, For someone reasons ```rollback`` may fail dropping table. — devnull, Aug 02 '16 at 03:24
@xdevnull perfect, I've check and you're right, the `navigation` table doesn't get removed and for that it was failing, thanks for both tips. — ReynierPM, Aug 02 '16 at 03:28

score 1 · Accepted Answer · answered Aug 02 '16 at 03:28

SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'navigation' already exists

Means you have same table name in database

So you need to verify, you don't have any table with that name in the database.

Make sure no table name with the same name
Run migration again

php artisan migration:rollback Sometimes fails to drop table for different reasons.

How to create a self-reference relationship (foreign key) in a Laravel migration?

1 Answers1