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

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

CodePudding user response：

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

CodePudding user response：

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