errno: 150 "Foreign key constraint is incorrectly formed" in Laravel

Question

Schema::create('position', function (Blueprint $table) {
        $table->increments('post_id');
        $table->String('post_name');
        $table->timestamps();
    });

Schema::create('candidate', function (Blueprint $table) {
        $table->increments('id');
        $table->String('name');
        $table->String('branch');
        $table->unsignedInteger('post_id');
        // $table->foreign('post_id_no')->references('post_id')->on('position')->onDelete('cascade');
        $table->foreign('post_id')->references('post_id')->on('position')->onDelete('cascade');
        $table->integer('count')->default(0);
        $table->timestamps();
    });

I have two tables, position and candidate. When I migrate I get error for foreign key. can anyone say whats error in code?

This is error I get when I migrate:

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1005 Can't create table voting.#sql-16b7_2b (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table candidate add constraint candidate_post_id_foreign foreign key (post_id) references position (post_id) on delete cascade)

catch (Exception $e) {
         throw new QueryException(
              $query, $this->prepareBindings($bindings), $e
          );
      }

PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table `voting`.`#sql-16b7_2b` (errno: 150 "Foreign key constraint is incorrectly formed")")

Answer 1

Likely a mismatch on the foreign key column and the referencing column possibly not being the same type or length. Instead of

$table->unsignedInteger('post_id');

in your candidate table, try:

$table->integer('post_id')->unsigned()->index();

Also, it is sometimes helpful and more clear (perhaps to mysql) to keep the original (position) id as just 'id'. You can call it post_id in the candidate table and reference 'id' on position. A little easier to understand.

Answer 2

You need to specify your default-engine as

$table->engine = 'InnoDB';

And make sure you have created the position table which the table candidate is referencing to. Please add your voting table schema in the post too.

Answer 3

You can try this.

$table->integer('post_id')->unsigned();
$table->foreign('post_id')->references('post_id')->on('position')->onDelete('cascade');

This worked for me. Thank you.

Answer 4

public function up() {     Schema::create('companies', function (Blueprint $table) {         $table->bigIncrements('id');         $table->string('name');         $table->text('address');         $table->string('tel1');         $table->string('tel2');         $table->integer('owner');         $table->unsignedBigInteger('access_id');         $table->string('depot_number')->default(2);         $table->timestamps();           $table->foreign('access_id')->references('id')->on('accesses')             ->onDelete('cascade');     }); } 

public function up() {     Schema::create('accesses', function (Blueprint $table) {         $table->bigIncrements('id');         $table->string('type');         $table->string('description');         $table->timestamps();     }); } 

In your database/migrations folder, sort by name. Then make sure create_accesses_table is before create_companies_table: enter image description here