0

I have a migration like this:

  public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->text('name');
            $table->unsignedSmallInteger('category_id');
            $table->unsignedInteger('section_d');
            $table->unsignedBigInteger('order');

            $table->timestamps();

            $table->foreign('category_id')
                ->references('id')->on('categories')
                ->onDelete('restrict');
                
            $table->foreign('section_id')
                ->references('id')->on('sections')
                ->onDelete('restrict');
        });
    }

There are other migrations that work fine, but everytime "php artisan migrate" is ran it shows this error regarding the specific migration above. Error:

   Illuminate\Database\QueryException  : 
   SQLSTATE[HY000]: General error: 1005 
   Can't create table `api`.`posts` (errno: 150
   "Foreign key constraint is incorrectly formed") 
   (SQL: alter table `categories` 
   add constraint `categories_category_id_foreign` 
   foreign key (`category_id`)
   references `categories` (`id`) on delete restrict)

Do you know why?

Bernard
  • 81
  • 8
  • You should read https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html , especially `Conditions and Restrictions` part. The column used on the foreign key should be indexed – Ergest Basha May 03 '22 at 14:56
  • Also make sure that the field definitions match. If `id` on `categories` is an unsigned big integer (or was created as bigIncrements), then `category_id` needs to be as well. – aynber May 03 '22 at 15:04
  • Thanks. Both tables have the id's created like " $table->bigIncrements('id'); ". – Bernard May 03 '22 at 15:08
  • 1
    Then `$table->unsignedSmallInteger('category_id');` needs to be `$table->unsignedBigInteger('category_id');` to make the columns match. – aynber May 03 '22 at 15:10
  • Thanks but even with that change it still shows the same error. – Bernard May 03 '22 at 15:19
  • And its only for the category_id column, for the section_id the error don't output nothing. – Bernard May 03 '22 at 15:20
  • Now I deleted all code from the up() of the migration and it shows the error " Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') default character set utf8mb4 collate 'utf8mb4_unicode_ci'' at line 1 (SQL: create table `posts` () default character set utf8mb4 collate 'utf8mb4_unicode_ci')". – Bernard May 03 '22 at 15:35
  • Does this answer your question? [MySQL 5.5 add foreign key fails with errors \[HY000\]\[150\] and \[HY000\]\[1005\]](https://stackoverflow.com/questions/8189779/mysql-5-5-add-foreign-key-fails-with-errors-hy000150-and-hy0001005) – Oleg Ushakov May 06 '22 at 13:54

0 Answers0