Tesly Tesly - 5 months ago 40
MySQL Question

Cannot add foreign key constraint - Laravel Entrust

I really don't know what is wrong, but I cannot add foreign key constraint with this query.

alter table `__acc_role_user` add constraint `__acc_role_user_user_id_foreign` foreign key (`user_id`) references `__acc_accounts` (`account_id`) on delete cascade on update cascade


How I'm creating the accounts table?

Schema::create('__acc_accounts', function($table)
{
$table->integer('account_id')->increments();
$table->integer('points')->default(0);

$table->foreign('account_id')->references('id')->on('accounts')->onDelete('cascade');
});


And then Entrust Migration (only the problematic section is below):

Schema::create('__acc_role_user', function (Blueprint $table) {
$table->integer('user_id')->unsigned();
$table->integer('role_id')->unsigned();

$table->foreign('user_id')->references('account_id')->on('__acc_accounts')
->onUpdate('cascade')->onDelete('cascade');
$table->foreign('role_id')->references('id')->on('__acc_roles')
->onUpdate('cascade')->onDelete('cascade');

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

Answer

This sintax is better:

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('__acc_accounts', function($table)
    {
        $table->integer('account_id')->increments();
        $table->integer('points')->default(0);
    });

    Schema::table('__acc_accounts', function(Blueprint $table)
{
        $table->foreign('account_id')->references('id')->on('accounts')->onDelete('cascade');
    });
}


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

And very important! In your migration order must be: first create the table 'accounts' and after '__acc_accounts'.

Tables with foreign keys should be created after the tables they reference to have been created.

This http://blog.kongnir.com/2015/03/08/laravel-order-of-migrations-with-foreign-keys/ can help you tu understand this.