Question 17166 in Laravel Question posted in
The official Laravel Documentation can be found here.

How make Laravel Migration for one to many and one to one for same table

I have a problem
to make migration with relationship.

case :

  • 1 Department will have many Employees
  • 1 Employee belongTo 1 Department
  • 1 Department will have one manager (from employees table)

Here is my migration

Schema::create('departments', function (Blueprint $table) {
        $table->id();
        $table->string('name');

        $table->foreignId('manager_id')->nullable()
            ->references('id')->on('employees')
            ->nullOnDelete();
        $table->timestamps();
    });

Schema::create('employees', function (Blueprint $table) {
    $table->id();
    $table->string('name', 255)->nullable();
    $table->string('picture', 1024)->nullable();

    $table->foreignId('user_id')->nullable()
        ->references('id')->on('users')
        ->nullOnDelete();

    $table->foreignId('department_id')->nullable()
        ->references('id')->on('departments')
        ->nullOnDelete();

    $table->timestamps();
});

when I do php artisan migrate:fresh
it show up error :

    SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Foreign key
    'departments_manager_id_foreign' references invalid table 'employees'. 
    (SQL: alter table "departments" add constraint "departments_manager_id_foreign" foreign key ("manager_id")
    references "employees" ("id") on delete set null)

This because there is no employees table when trying to create departments.
But if I trying to create employees first, then no departments tables.

Any suggestion for my problem ?

Thank you

Tags:

2

Answers


  1. 0 
    Schema::create('departments', function (Blueprint $table) {
        $table->increments("id")->unsigned(false);
        $table->string('name');

        $table->unsignedInteger('manager_id')->value(11)->nullable(true);
        $table->foreign('manager_id')->references('id')->on('employees')->onDelete('cascade');
        $table->timestamps();
    });

    Schema::create('employees', function (Blueprint $table) {
    $table->increments("id")->unsigned(false);
    $table->string('name', 255)->nullable();
    $table->string('picture', 1024)->nullable();

    $table->unsignedInteger('user_id')->value(11)->nullable();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
     
    $table->unsignedInteger('department_id')->value(11)->nullable();
    $table->foreign('department_id')->references('id')->on('departments')->onDelete('cascade');

    $table->timestamps();
});

    this should work

    Login or Signup to reply.
  2. 0

    as @TimLewis suggested in the comment, you can add the department table without foreign key first, then add the employees table and setup the foreign key for the departments table.

    Department migration

    Schema::create('departments', function (Blueprint $table) {
        $table->id();
        $table->string('name');

        $table->unsignedBigInteger('manager_id')->nullable();

        $table->timestamps();
    });

    The employees migration

    Schema::create('employees', function (Blueprint $table) {
    $table->id();
    $table->string('name', 255)->nullable();
    $table->string('picture', 1024)->nullable();

    $table->foreignId('user_id')->nullable()
        ->references('id')->on('users')
        ->nullOnDelete();

    $table->foreignId('department_id')->nullable()
        ->references('id')->on('departments')
        ->nullOnDelete();

    $table->timestamps();
});
Schema::table('departments', function (Blueprint $table) {
    $table->foreign('manager_id')->references('id')->on('employees')
            ->nullOnDelete();
});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search