Laravel many to one same table migration

Question

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 ''

Possible duplicate of [Migration: Cannot add foreign key constraint in laravel](https://stackoverflow.com/questions/22615926/migration-cannot-add-foreign-key-constraint-in-laravel) — Samsquanch, Aug 31 '17 at 16:29
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, Aug 31 '17 at 16:36

score 1 · Answer 1 · answered Nov 07 '19 at 09:19

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');
});

score 0 · Answer 2 · answered Aug 31 '17 at 16:33

0

field id is using unsigned integer data type

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

answered Aug 31 '17 at 16:33

Yusuf Eka Sayogana

391
2
10

score 0 · Accepted Answer · answered Aug 31 '17 at 16:35

0

Change following line

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

to

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

Since foreign key id should be unsigned integer

answered Aug 31 '17 at 16:35

Sagar Gautam

9,049
6
53
84

1

This change with my code runs perfectly. Thank you. – Sergio Barbosa Aug 31 '17 at 20:33
@SergioBarbosa I'm happy to help you ! – Sagar Gautam Sep 01 '17 at 02:44

Govind Samrow · Answer 4 · 2017-08-31T16:46:50.360

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');
  });

}

avatars_id must be unsigned. i think use Schema::table after Schema::create is not efficient because u cant set the foreign key inside Schema::create — Yusuf Eka Sayogana, Aug 31 '17 at 17:38

Laravel many to one same table migration

4 Answers4