diceline-chartmagnifiermouse-upquestion-marktwitter-whiteTwitter_Logo_Blue

Today I Learned

SQLite vs MySQL - foreign key checks

Sqlite does not check for foreign key integrity when creating tables, only when inserting records. However, MySQL does check in both cases.

To make this more clear, let's take a look at a simple example: given two tables - todos and users - created in this exact order:

 Schema::create('todos', function (Blueprint $table) {
      $table->id();
      $table->string('title');
      $table->unsignedBigInteger('user_id');
      $table->foreign('user_id')
          ->references('id')
          ->on('users');
  });
 Schema::create('users', function (Blueprint $table) {
      $table->id();
      $table->string('username');
      $table->string('password');
  });

If you run your migrations using the sqlite driver, everything works just fine.

However, if you run your migrations using the mysql driver, you get the following error:

SQLSTATE[HY000]: General error: 1005 Can't create table `[your-app]`.`todos` 
(errno: 150 "Foreign key constraint is incorrectly formed") 
(SQL: alter table `todos` add constraint `todos_user_id_foreign`
 foreign key (`user_id`) references `users` (`id`))