Many to Many relation with unique records in pivot

If you don't want multiple records, why are you using a many to many relationship to begin with? :)

I just want unique attachment.

For example : A user can have a newsletter subscription on multiple kink of newsletter (type). And a newsletter (type) can have many users registered. So, I need to be sure that a user hasn't subscribe several times to a newsletter.

(it's just an example)

When the combination should be unique, you can remove the 'id' column from the pivot table. An 'id' column is only necessary if other fields in your table can't make up a unique value. Try removing the id column and making the primary key of your table a combination of model1 and model2

@stidges : Thanks !

So, if I use this : https://github.com/JeffreyWay/Laravel-4-Generators#pivot I should update the migration file to this ? :

class CreateOrderUserTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('order_user', function(Blueprint $table) {

            $table->integer('order_id')->unsigned()->index();
            $table->foreign('order_id')->references('id')->on('orders')->onDelete('cascade');
            $table->integer('user_id')->unsigned()->index();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->timestamps();
        });
    }


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

}

Yep! To really enforce the unique constraint, you could also add this:

$table->primary(['order_id', 'user_id']);

Ok. So it's the couple order_id + user_id that will be unique ?

Yeah, a primary key MUST be unique, so if you place that on those two columns, the combination has to be unique.

Many thanks Stidges

Now when a duplicate insert occurs I get a SQL error and the app crashes. How should I prevent this.

@GiantCowFilms

Filter user requests. Make use of "validators" to validate user inputs and apply various useful rules to it like uniqueness to a certain table and require a certain field to be numeric and such. You can also customize error messages returned from failed validations. Just make sure you control what inputs goes into your database.

Just use sync instead of attach

For anyone else bumping on this thread:

->sync([userID], false) the falseparameter means sync without detaching.

Since L5.1 you can also use syncWithoutDetaching

@stidges I am trying to implement this in my Laravel application, but I am struggling to use a unique constraint. I have a many to many relation with Competition and Location, and use the pivot model CompetitionLocation.

I need a unique constraint to check if a competition already has a relation with a certain location (the one that is being added).

I've checked the database up on adding a certain location to a competition which it already has a relation with. when doing so there is no duplicate entry inserted so this is fine. But, i would like to use the eloquent validate() function to return an error message if so

I use $competition->locations()->attach($locationId->id); to make the relation.

and have this in my migration

public function up()
    {
        Schema::create('competition_locations', function (Blueprint $table) {
            $table->primary(['competition_id', 'location_id']);
            $table->integer('competition_id')->unsigned()->index();
            $table->foreign('competition_id')->references('id')->on('competitions');
            $table->integer('location_id')->unsigned()->index();
            $table->foreign('location_id')->references('id')->on('locations');
            $table->timestamps();
        });
    }