1005 Can't create table `portal`.`employees` (errno: 150 "Foreign key constraint is incorr-CodePudding

Hey guys I tried too much stuff and read some blogs or discussion I didn't fix my problem I'm new in laravel this project. I got error when I want to create to database this error like

PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `portal`.`employees` (errno: 150 "Foreign key constraint is incorrectly formed")")

my migration for payments:

  public function up()
{
    Schema::create('payments', function (Blueprint $table) {
        $table->bigInteger('id');
        $table->unsignedBigInteger('employee_id');
        $table->decimal('salary',16);
        $table->decimal('amount_paid',16);
        $table->unsignedBigInteger('paid_by');
        $table->string('remark');
        $table->string('department',50);
        $table->timestamps();
    });
    Schema::table('payments', function($table) {
        $table->foreign('employee_id')->references('id')->on('employees')->onUpdate('cascade')->onDelete('cascade');

        $table->foreign('paid_by')->references('id')->on('users');
    });
}

my migration for employees:

    public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->unsignedBigInteger('id');
        $table->string('name');
        $table->string('department');
        $table->string('location');
        $table->string('telephone');
        $table->decimal('salary',16);
        $table->string('cover_image');
          $table->foreign('department')->references('name')->on('departments')->onUpdate('cascade')->onDelete('cascade');
        $table->timestamps();
    });

Department Table:

   public function up()
{
    Schema::create('departments', function (Blueprint $table) {
        $table->bigInteger('id');
        $table->string('name');
        $table->timestamps();
    });
}

my migration folder: https://i.stack.imgur.com/pCsbg.png

CodePudding user response：

You can simply set the foreign key on the department Id and of course, it's much better and has better performance setting a foreign key on unique keys.

CodePudding user response：

making foreign keys on non unique columns is not a good idea, and only innodb for mysql support that, see mysql doc.

instead of:

  $table->foreign('department')->references('name')->on('departments')->onUpdate('cascade')->onDelete('cascade');

you should make the foreign key regularly like:

 $table->bigInteger('department_id');
 $table->foreign('department_id')->references('id')->on('departments')->onUpdate('cascade')->onDelete('cascade');