0

I've read EVERY article or posts about this and nothing solved my problem with LARAVEL. I get duplicate with error on raw MySQL, but I don't have difficulties with raw MySQL. I want to do it correctly with the Laravel Migrations I get this error:

    SQLSTATE[HY000]: General error: 1005 Can't create table `u129714830_ipc`.`employeesTbl` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `employeesTbl` add constraint `employeestbl_roleid_foreign` foreign key (`roleId`) references `roleIds` (`id`))

  at C:\Programmation\Board\vendor\laravel\framework\src\Illuminate\Database\Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

  1   C:\Programmation\Board\vendor\laravel\framework\src\Illuminate\Database\Connection.php:501
      PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `u129714830_ipc`.`employeesTbl` (errno: 150 "Foreign key constraint is incorrectly formed")")

  2   C:\Programmation\Board\vendor\laravel\framework\src\Illuminate\Database\Connection.php:501
      PDOStatement::execute()

I have a migration name "2022_09_26_180820_employee_tbl_creation.php" to add the table employeesTbl in which there is this

        Schema::create('employeesTbl', function (Blueprint $table) {
        $table->string('employeeId')->primary();
        $table->string('employeeFirstName');
        $table->string('employeeLastName');
        $table->date('employeeEntryDate')->default(DB::raw('CURRENT_TIMESTAMP'));
        $table->date('employeeBirthDate');
        $table->string('employeeEmail')->unique();
        $table->string('employeePhone');
        $table->string('employeePassword');
        $table->string('employeeSalt');
        $table->string('employeeEmailConfirmed');
        $table->integer('roleId');
        $table->boolean('employeeActive');
        $table->timestamps();
    });

I also have a migration named "2022_09_26_213500_roles_tbl_creation.php" to add the table rolesTbl in which there is this:

        Schema::create('rolesTbl', function (Blueprint $table) {
        $table->id('roleId');
        $table->string('roleName');
        $table->longtext('roleDescription');
        $table->timestamps();
    });

And finally I have this migration named "2022_09_26_225043_add_foreign_key.php" to add the foreign keys in which there is:

    Schema::table('employeesTbl', function (Blueprint $table) {
        $table->foreignId('roleId')->constrained();
    });

I've tried every way possible to do it, the Laravel 6.x way, 7.x way and the 8.x way. I've read and watched all of this:

  1. https://www.youtube.com/watch?v=OOLIx9-_tl4
  2. Migration: Cannot add foreign key constraint
  3. https://laravel.com/docs/9.x/migrations
  4. Laravel migration: "Foreign key constraint is incorrectly formed" (errno 150)
  5. https://laracasts.com/discuss/channels/laravel/foreign-key-constraint-is-incorrectly-formed-7

And much more can someone please help me, this is not an order error, I tried to migrate the role migration before the employee and vise versa nothing work. I also tried this way of doing it

$table->foreign('roleId')->references('roleId')->on('rolesTbl');
Dolotboy
  • 74
  • 7
  • My question has been edited to precise that it has nothing to do with SQL error but more with Laravel Migration Errors – Dolotboy Sep 26 '22 at 23:41
  • I think you need to change in rolesTbl table of column roleId to id and use this id column is use for foreign key assignment in other table. – NIKUNJ PATEL Sep 29 '22 at 12:59
  • But if I wanna do it with a string, like in some cases (Other tables) the id for ex: companyId is a string like STR1234, can I use it as a foreign key, or it must be of type id ? – Dolotboy Sep 30 '22 at 02:26
  • Yes you can do with string type also but naming convention should follow. Also the foreign key must need to unique type for foreign key assignment. You can add unique() type of foreign key. – NIKUNJ PATEL Sep 30 '22 at 04:34

0 Answers0