I got the the problem, that the id's of the FK's for my pivot table get mixed up before insert and thus I get an error because a category with an ID that high isn't available.

Here's how I try to insert:

$newAdvertisement = new Advertisement();

$newAdvertisement->name = $request->name;
$newAdvertisement->save();
$newAdvertisement->advertisementCategories()->attach($request->selectedCategory);

The Advertisement migration and class function:

public function advertisementCategories(): BelongsToMany
{
    return $this->belongsToMany(
        AdvertisementCategory::class, 
        'advertisement2advCategory',
        'advertisementCategory_id',
        'advertisement_id'
    );
}

//Advertisement
Schema::create('advertisement', function (Blueprint $table) {
    $table->id();
    $table->string('name')->default('');
});

And here the category class function and migration:

public function advertisements(): BelongsToMany
{
    return $this->belongsToMany(
        Advertisement::class,
        'advertisement2advCategory',
        'advertisement_id',
        'advertisementCategory_id'
    );
}

Schema::create('advertisement2advCategory', function (Blueprint $table) {
    $table->foreignId('advertisement_id');
    $table->foreign('advertisement_id')
        ->references('id')
        ->on('advertisement')
        ->onDelete('cascade');

    $table->foreignId('advertisementCategory_id');
    $table->foreign('advertisementCategory_id')
        ->references('id')
        ->on('advertisementCategory')
        ->onDelete('cascade');
});

In the error message you can clearly see, that the advertisement_id and advertisementCategory_id get mixed up... In this example an advertisement with the ID 19 has been created and the selected category (that exists) has ID 4, but the generated sql is trying to insert an advertisement with id 4 and a cagetory with id 19. Just How?

I'm using the latest version of laravel and php 8.1.1

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (umarketdb.advertisement2advCategory, CONSTRAINT advertisement2advcategory_advertisementcategory_id_foreign FOREIGN KEY (advertisementCategory_id) REFERENCES advertisementCategory (i) (SQL: insert into advertisement2advCategory (advertisementCategory_id, advertisement_id`) values (19, 4))

CodePudding user response：

You need to switch the order of the 3rd and 4th arguments to belongsToMany. It is a little confusing with how things are named but as per the documentation:

"The third argument is the foreign key name of the model on which you are defining the relationship, while the fourth argument is the foreign key name of the model that you are joining to." - Laravel 8.x Docs - Eloquent - Relationships - Many to Many - Model Structure

So, if you are defining a relationship on the Category model to Advertisement lets say then the 3rd argument is the foreign key that references categories (category_id). The 4th argument is the foreign key that references the other model/table advertisements (advertisement_id).