CONSTRAIN Unique on Laravel 5 Schema Builder

Question

First of all, I want to tell you, I searched the Internet for this problem, and I could not solve this problem.

My problem is with the main keys of my tables.

I have 2 tables, events and event_guest

+-----------+ | event | +-----------+ | eventid | +-----------+ +-------------+ | event_guest | +-------------+ | guestid | | eventid | | firstname | | lastname | | email | +-------------+ 

Each event has a lot of event_guest , but each guest’s email should be unique for each event.

On my controller, I want to get or create using the firstOrCreate method in EventGuest Model:

 EventGuests::firstOrCreate(['eventid' => $eventid,'firstname' => 'Ivan', 'lastname'=>'Bravo', 'email'=>' [e-mail] '); 

If the message is not set in the event, it should record the record.

 +-----------+----------+-----------+-----------+----------------+ | guestid | eventid | firstname | lastname | email | +-----------+----------+-----------+-----------+----------------+ | 1 | 1 | Ivan | Bravo | [e-mail] | +-----------+----------+-----------+-----------+----------------+ 

I need eventid as primary key and guestid as auto_increment that it can reset to 1 in every event. For instance:

 +-----------+----------+-----------+-----------+----------------+ | guestid | eventid | firstname | lastname | email | +-----------+----------+-----------+-----------+----------------+ | 1 | 1 | Ivan | Bravo | [e-mail] | | 2 | 1 | John | Doe | [e-mail] | | 1 | 2 | Ivan | Bravo | [e-mail] | +-----------+----------+-----------+-----------+----------------+ 

And I also need email as a unique field to prevent line duplication.

I am currently using Laravel 5 Migrations, but when I try to use the reset primary fields, it asks for this error:

 Multiple primary key defined (SQL: alter table `event_guest` add primary key event_guest_eventid_guestid_primary(`eventid`, `guestid`)) 

This is my transition code:

  Schema::create('event_guest', function(Blueprint $table) { $table->integer('guestid', true); $table->integer('eventid'); $table->integer('contactid')->nullable(); $table->string('firstname', 256); $table->string('lastname', 256); $table->string('email', 256); $table->unique( array('email','name') ); $table->primary(array('eventid','guestid')); }); 

I really need help to get it right.

In the past, I had no problems:

 create table `event_guest` ( `guestid` int(11) NOT NULL AUTO_INCREMENT, `eventid` int(11) NOT NULL DEFAULT '0', `firstname` varchar(255) NOT NULL, `lastname` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, PRIMARY KEY (`eventid`,`guestid`), CONSTRAINT guest UNIQUE (eventid,email) ) ENGINE=MyISAM 

Now

Answer 1

There are 2 problems in your transition code that prevent you from creating the necessary table.

First of all, the default MySQL engine is InnoDB, which prohibits auto-increment on composite primary keys. Therefore, you need to explicitly set the mechanism in MyISAM:

 $table->engine = 'MyISAM'; 

Secondly, when you set a column for auto-increment, Laravel schema builder assumes that it is the primary key and makes this column the primary key when creating the create statement. Therefore, when the next query tries to determine the composite primary key, the primary key already exists in the guestid column - therefore, the primary key error is repeated.

The solution to this problem is to define guestid as a standard integer column, create a composite key, and then update the guestid column so that it is auto-incrementing. Unfortunately, the schema builder does not support any other operations than renaming, so you need to use the base level of the database and execute the raw query.

To summarize, your transition code should look like this:

 Schema::create('event_guest', function(Blueprint $table) { $table->engine = 'MyISAM'; $table->integer('guestid'); $table->integer('eventid'); $table->integer('contactid')->nullable(); $table->string('firstname', 256); $table->string('lastname', 256); $table->string('email', 256); $table->unique( array('email','name') ); $table->primary(array('eventid','guestid')); }); DB::statement('ALTER TABLE event_guest MODIFY guestid INTEGER NOT NULL AUTO_INCREMENT'); 

Answer 2

I understand what you are asking. But I think that you are creating too much redundancy in your database. Your situation affects the ManyToMany relationship between the event and the guest. What I believe will greatly simplify your problem. Therefore, I present a different solution from what you are asking.

In your Event issue, there may be many Guest s, and Guest may belong to many Event s. We can imagine this in Laravel 5. *, as shown below,

Migration and Model for Event

 Schema::create('events', function(Blueprint $table){ $table->increments('id')->unsigned(); $table->string('title'); $table->string('venue'); $table->text('description'); $table->timestamps(); }); class Event extends Model{ public function guests(){ return $this->belongsToMany('App\Guest', 'event_guests'); } } 

Migration and Model for Guest

 Schema::create('guests', function(Blueprint $table){ $table->increments('id')->unsigned(); $table->string('email')->unique(); $table->string('password'); $table->string('first_name'); $table->string('last_name'); $table->timestamps(); }); class Guest extends Model{ public function events(){ return $this->belongsToMany('App\Event','event_guests'); } } 

Migration for event_guests

 Schema::create('event_guests', function(Blueprint $table){ $table->increments('id'); $table->integer('event_id')->unsigned(); $table->integer('guest_id')->unsigned()->nullable(); $table->foreign('guest_id')->references('id')->on('guests')->onDelete('cascade'); $table->foreign('event_id')->references('id')->on('events')->onDelete('cascade'); }); 

Now Guest 's Event subscription will be as simple as below

 $event = Event::create([ 'title' => 'Laracon', 'venue' => 'Hotel XYZ, New York', 'description' => 'Drink is not free' ]); $guest = Guest::findOrFail($guestId); $guest2 = Guest::findOrFail($guestId2); $event->guests()->sync([$guestId->id, $guestId2->id], false); //`false` as second argument prevents `sync` from detaching previous associations 

In this setting, you can split Event and Guest data into selected tables without duplication and it is very easy to maintain uniqueness constraints. And you create a pivot table to maintain your ships relationship.

Learn more about relationships with Laravel.