Laravel mysql error relational databases?-CodePudding

Syntax error or access violation: 1072 Key column 'collection_id' doesn't exist in table (SQL: alter table products add constraint products_collection_id_foreign foreign key (collection_id) references collections (id) on delete cascade)

So im having this issue trying to relation products with collections with a collection_id.

This is my collection migration

    class CreateCollectionsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('collections', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->string('name');
            $table->string('author');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('collections');
    }
}

This is my product migration

class CreateProductsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->string('name');
            $table->integer('price');
            $table->string('src');
            $table->unsignedBigInteger('collection_id');
            $table->foreign('collection_id')->references('id')->on('collections')->onDelete("cascade");
            $table->integer('amount');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('products');
    }
}

CodePudding user response：

are your migrations running correctly you might also check the migrations sequence on creating the table if the collection is being created first before the products, if products are created before collection it throws this error because the collection is not yet created, also there's a shortcut for defining foreign keys which is $table->foreignId('collection_id')->constrained()->cascadeOnDelete();

CodePudding user response：

You can do it the following way.

$table->unsignedBigInteger('collection_id');
$table->foreign('collection_id')->references('id')->on('collections')->onDelete("cascade");

And one more thing run the following command :

php artisan migrate:fresh

 php artisan migrate:refresh

hope your problem will be resolved.or can follow the following URL Laravel migration: "Foreign key constraint is incorrectly formed" (errno 150)