Laravel Foreign key constraint is incorrectly formed after adding $table->engine = 'MyISAM';

Question

After adding $table->engine = 'MyISAM'; seem foreign key is not working anymore. This my main table

public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->engine = 'MyISAM';
$table->integer('id');
$table->string('user_id',20);
$table->string('name');
$table->primary(['user_id','id']);
$table->rememberToken();
$table->timestamps();
});
DB::statement('ALTER TABLE users CHANGE id id INT(11) NOT NULL 
AUTO_INCREMENT');
}

Foreign key table

public function up()
{
    Schema::create('useraccesses', function (Blueprint $table) {
        $table->increments('id');
        $table->string('user_id',20);
        $table->datetime('accessTime')->nullable();
        $table->datetime('quittime')->nullable();
        $table->datetime('activeHour')->nullable();
        $table->text('token')->nullable();
        $table->foreign('user_id')
        ->references('user_id')->on('users')
        ->onDelete('cascade');
        $table->timestamps();
    });
}

Thanks

MyISAM doesn't support foreig keys https://stackoverflow.com/a/12971358/7498116 — porloscerros Ψ, Jun 05 '19 at 07:23
Thanks, But How do i set multi key with id auto column without set engine myisam? — 21bn, Jun 05 '19 at 07:34

score 0 · Answer 1 · answered Jun 05 '19 at 07:21

0

Foreign keys are not supported for MyISAM tables in MySQL. Use InnoDB tables instead. c.f. https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

answered Jun 05 '19 at 07:21

anaxamaxan

338
2
7

score 0 · Answer 2 · answered Jun 05 '19 at 12:53

Try this

for users migration
...
$table->increments('id');
...

for useraccesses migration
...
$table->integer('user_id')->unsigned()->index();
$table->foreign('user_id')
        ->references('user_id')->on('users')
        ->onDelete('cascade');
...

Laravel Foreign key constraint is incorrectly formed after adding $table->engine = 'MyISAM';

2 Answers2