Composite foreign key Laravel

Question

I'm finding it difficult to create a composite foreign key. I would like a session table that has both 'movieid' and 'cinemaid' as a composite foreign key. This is because one session would require both the movie and the cinema location.

My current schema is the folllowing:

Schema::create('session', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('movieId');
    $table->integer('cinemaId');
    $table->foreign(array('movieId', 'cinemaId'))->references(array('id', 'id'))->on(array('movies', 'cinema'));
    $table->dateTime('time');
});

I can't find much information on how to create composite foreign keys in laravel. The best thing I've found:

http://www.geexie.com/composite-primary-foreign-keys-laravel/

However, in this example they pull both foreign keys from one table, where-as in my above example you can see that I need to get data from both the "movies" table and the "cinemas" table. I thought maybe using on(array('movies', 'cinema') would work, but it comes up with the error "Array to string conversion".

I've tried removing the (array()) part, but it doesn't work that way either.

Answer 1

A foreign key links to one other table. So you need two separate foreign keys, one for movies and one for cinema.

$table->foreign('movieId')->references('id')->on('movies');
$table->foreign('cinemaId')->references('id')->on('cinema');

Also, I'm guessing you want a composite index in the session table, on two fields movieId and cinemaId. If so, you need to decide whether to have the new composite index as the primary index on session or not.

If you want the composite index to be your primary index, then you don't also need the id field, so you need to remove the $table->increments('id') line. You would end up with something like this:

Schema::create('session', function (Blueprint $table) {

$table->integer('movieId');
$table->integer('cinemaId');
$table->primary(['movieId', 'cinemaId']);  // note, this is a *primary* key
$table->foreign('movieId')->references('id')->on('movies');
$table->foreign('cinemaId')->references('id')->on('cinema');
$table->dateTime('time');

});

Or, if you want to keep the id as the primary index, then you just want the composite index to be a regular old index. So you might do something like this:

Schema::create('session', function (Blueprint $table) {

$table->increments('id');
$table->integer('movieId');
$table->integer('cinemaId');
$table->index(['movieId', 'cinemaId']);  // This is an index, but *not* a primary key
$table->foreign('movieId')->references('id')->on('movies');
$table->foreign('cinemaId')->references('id')->on('cinema');
$table->dateTime('time');

});

Does that help?