Laravel migration foreign keys references to a primary key with two columns?

Question

I am using Laravel 5.3 and MySQL.

How can I add in Laravel foreign keys references to a primary key with two columns?

Below are my migration scripts (under the database/migrations/ directory):

primary key with two columns

public function up()
{
    Schema::create('X', function (Blueprint $table) {
        $table->integer('a')->unsigned();
        $table->integer('b')->unsigned();
        $table->primary(['a', 'b']);
        $table->timestamps();
    });
}

and in another,

public function up()
{
    Schema::create('Y', function (Blueprint $table) {
        $table->increments('k');
        $table->foreign('c')->references(['a', 'b'])->on('X')->onDelete('cascade');
        $table->timestamps();
    });
}

However, it doesn't work so: how can I achieve that?

Answer 1

Use Schema::table() instead of Schema::create() when adding foreign key constraints to your database.

Below, snippets illustrating the fix:

// File name: 2016_09_28_create_x_table.php
public function up()
{
    // Create table X
    Schema::create('X', function (Blueprint $table) {
        $table->integer('a')->unsigned();
        $table->integer('b')->unsigned();
        $table->primary(['a', 'b']);
        $table->timestamps();
    });
}


// File name: 2016_09_28_create_y_with_foreignkey_table.php
public function up()
{
    // Create table Y
    Schema::create('Y', function (Blueprint $table) {
        $table->increments('k');
        $table->integer('c')->unsigned();
        $table->timestamps();
    });

    // Add Foreign key
    Schema::table('Y', function (Blueprint $table) {
        $table->foreign('c')->references('a')->on('X')->onDelete('cascade');
    });
}

Remember unsigned() should be applied on the c.

Answer 2

This is the only way that I found to simulate composite keys and FK pointing to composite keys working in Laravel 5.3 - I miss a more compacted solution in Laravel.

Anyway, here is my code

// File name: 2016_09_28_create_x_table.php
public function up()
{
    // Create table X
    Schema::create('X', function (Blueprint $table) {
        $table->increments('j');
        $table->integer('a')->unsigned();
        $table->integer('b')->unsigned();
        $table->unique(['a', 'b']);

        $table->timestamps();
    });
}


// File name: 2016_09_28_create_y_with_foreignkey_table.php
public function up()
{
    // Create table Y
    Schema::create('Y', function (Blueprint $table) {
        $table->increments('k');
        $table->integer('c')->unsigned();
        $table->timestamps();
    });

    // Add Foreign key
    Schema::table('Y', function (Blueprint $table) {
        $table->foreign('c')->references('j')->on('X')->onDelete('cascade');
    });
}

Answer 3

Foreign key should be set for 2 columns. foreign() should get also array

public function up()
{
    Schema::create('X', function (Blueprint $table) {
        $table->integer('a')->unsigned();
        $table->integer('b')->unsigned();
        $table->primary(['a', 'b']);
        $table->timestamps();
    });
}

public function up()
{
    Schema::create('Y', function (Blueprint $table) {
        $table->increments('k');
        $table->integer('a');
        $table->integer('b');
        $table->foreign(['a', 'b'])->references(['a', 'b'])->on('X')->onDelete('cascade');
        $table->timestamps();
    });
}`