Home > database >  Illuminate\Database\QueryException SQLSTATE[HY000]: General error: 1005 Can't create table
Illuminate\Database\QueryException SQLSTATE[HY000]: General error: 1005 Can't create table

Time:08-14

I try to migrate "comment" table with foreing key from "blogPost" table, so I had this error about foring key: enter image description here

this is code error:

SQLSTATE[HY000]: General error: 1005 Can't create table laravel8.comments (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table comments add constraint comments_blog_post_id_foreign foreign key (blog_post_id) references blog_posts (id) on delete cascade)

this is code migration Table "Comment":

Schema::create('comments', function (Blueprint $table) {
        $table->id();
        $table->timestamps();
        $table->text('content');
        $table->Integer('blog_post_id')->index();
        $table->foreign('blog_post_id')->references('id')->on('blog_posts')->onDelete('cascade');
    });

this code for migration table "Blog_Post":

Schema::create('blog_posts', function (Blueprint $table) {
        $table->id();
        $table->timestamps();
        $table->string("title");
        $table->text("content");
    });

-How can I fixe it ? thank you

CodePudding user response:

use constrained.

The constrained method will use conventions to determine the table and column name being referenced. If your table name does not match Laravel's conventions, you may specify the table name by passing it as an argument to the constrained method:

Schema::create('comments', function (Blueprint $table) {
            $table->id();
            $table->text('content');
            $table->foreignId('blog_post_id')->constrained()->index()->onDelete('cascade');
            $table->timestamps();
        });

You see the implementation of constraint() below.

 /**
     * Create a foreign key constraint on this column referencing the "id" column of the conventionally related table.
     *
     * @param  string|null  $table
     * @param  string  $column
     * @return \Illuminate\Database\Schema\ForeignKeyDefinition
     */
    public function constrained($table = null, $column = 'id')
    {
        return $this->references($column)->on($table ?? Str::plural(Str::beforeLast($this->name, '_'.$column)));
    }

As issue facing because of id() method is bigInteger but you are using integer for blog_post_id in comments table

if you see id() implementation ,it uses bigIncrements

    /**
     * Create a new auto-incrementing big integer (8-byte) column on the table.
     *
     * @param  string  $column
     * @return \Illuminate\Database\Schema\ColumnDefinition
     */
    public function id($column = 'id')
    {
        return $this->bigIncrements($column);
    }
  • Related