Laravel many to one same table migration

2
votes

I have this code for migration 

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

class CreateAvatarsTable extends Migration
{

    public function up()
    {
        Schema::create('avatars', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name', 100)->nullable();
            $table->string('filename', 255)->nullable();
            $table->text('detail')->nullable();
            $table->text('pos_x')->nullable();
            $table->text('pos_y')->nullable();

            $table->integer('avatars_id')->nullable();
              $table->foreign('avatars_id')->references('id')->on('avatars')->onUpdate('cascade')->onDelete('cascade');

            $table->softDeletes();
            $table->timestamps();
        });
    }


    public function down()
    {
        Schema::dropIfExists('avatars');
    }
}

When I run the code for generate migrations

php artisan migrate:refresh

I got this result:

[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name '' (SQL: alter table avatars add con straint avatars_avatars_id_foreign foreign key (avatars_id) references `` (id) on delete cascade)

[Doctrine\DBAL\Driver\PDOException] SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name ''

[PDOException] SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name ''

4
phplaraveleloquent
Possible duplicate of Migration: Cannot add foreign key constraint in laravel - Samsquanch
Add the foreign key after the creation of the table, I normally create a migration for the table creation and one for indexes and keys. - dops

4 Answers

0
votes

Change following line 

$table->integer('avatars_id')->nullable();

to 

$table->integer('avatars_id')->unsigned();

Since foreign key id should be unsigned integer

1
votes

For assigning a foreign key in the same table you should use this, up to Laravel 5.8

Schema::table('avatars', function (Blueprint $table) {
   $table->unsignedBigInteger('avatars_id')->nullable();
});

Schema::table('avatars', function (Blueprint $table) {
  $table->foreign('avatars_id')->references('id')->on('avatars');
});
0
votes

field id is using unsigned integer data type

$table->unsignedInteger('avatars_id')->nullable();
0
votes

You are trying to set reference on table that not exist yet; try it as following:

public function up() {
  Schema::create('avatars', function(Blueprint $table) {
    $table - > increments('id');
    $table - > string('name', 100) - > nullable();
    $table - > string('filename', 255) - > nullable();
    $table - > text('detail') - > nullable();
    $table - > text('pos_x') - > nullable();
    $table - > text('pos_y') - > nullable();
    $table - > integer('avatars_id') - > nullable();
    $table - > softDeletes();
    $table - > timestamps();
  })

  Schema::table('avatars', function($table) {
    $table - > foreign('avatars_id') - > references('id') - > on('avatars') - > onUpdate('cascade') - > onDelete('cascade');
  });

}