CONSTRAIN Unique on Laravel 5 Schema Builder

Question

First of all, I want to tell you, I have searched the Internet this problem and I have not been able to solve this problem.

My problem is with the primary keys of my tables.

I have 2 Tables, events and event_guest

+-----------+
| event     |
+-----------+
| eventid   |
+-----------+

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

Each event has many event_guest, but the email of each guest must be unique on each event.

On my controller i want to get or create by using firstOrCreate method on EventGuest Model:

EventGuests::firstOrCreate(['eventid' => $eventid,'firstname' => 'Ivan', 'lastname'=>'Bravo', 'email'=>'ivnbrv@mac.com');

If the email is not set on the event it must record the entry.

+-----------+----------+-----------+-----------+----------------+
|  guestid  | eventid  | firstname | lastname | email           | 
+-----------+----------+-----------+-----------+----------------+
|         1 |        1 | Ivan      | Bravo     | ivnbrv@mac.com | 
+-----------+----------+-----------+-----------+----------------+

What I need it is eventid as the primary key and guestid as auto_increment field so it can reset to 1 on each event. For example:

+-----------+----------+-----------+-----------+----------------+
|  guestid  | eventid  | firstname | lastname | email           | 
+-----------+----------+-----------+-----------+----------------+
|         1 |        1 | Ivan      | Bravo     | ivnbrv@mac.com | 
|         2 |        1 | John      | Doe       | test@mac.com   | 
|         1 |        2 | Ivan      | Bravo     | ivnbrv@mac.com | 
+-----------+----------+-----------+-----------+----------------+

And and i also need email as an unique field to prevent duplicate rows.

Currently I'm using Laravel 5 Migrations, but when i try to reset primary fields it prompts this error:

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

This is my migration 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 understand this properly.

I had no trouble in the past using:

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

I understand what you are asking for. But I think you are creating too much redundancy in your database. Your situation warrents ManyToMany relationship between Event and Guest.Which I beleive, will simpify your problem to great extent. So I am presenting a different solution from what you are asking.

In your problem Event can have many Guests, and Guest can belong to many Events. We can present this in Laravel 5.* as 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 subscribing Guest to Event is as easy 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 stetagy, you can seperate event and guest data to dedicated tables without any duplication and maintain uniqueness constraints very easily. And you create pivot table to maintain their relation ships.

Read More about Laravel Relationships.

Answer 2

There are 2 issues with your migration code that prevent you from creating the table you need.

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

$table->engine = 'MyISAM';

Secondly, when you set a column to auto increment, Laravel's schema builder assumes it is the primary key and makes that column a primary key when generating create statement. So when the next query tries to define composite primary key there is already existing primary key on guestid column - hence the duplicate primary key error.

Solution to that problem is to define guestid as standard integer column, create composite key and then update guestid column so that it is autoincrement. Unfortunately schema builder does not support any alter operations other than renaming column, so you need to use the underlying DB layer and execute a raw query.

To sum up, your migration 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');