Foreign Key Error

[nabberuk]

I'm getting the following error when trying to migrate (php artisan migrate)

" SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL : alter table assigned_roles add constraint assigned_roles_user_id_foreig n foreign key (user_id) references users (id))"

I'm using mysql.

[dayaki]

Having same issues, solution?

[excetera]

that's because "users" table doesn't exist in your database. it is said that entrust work well with confide, so one way to go is install confide, migrate, and you will have "users" in your database. after that you can migrate your entrust.

[BryanHeath]

You can run this SQL to create the users table. You can also google how to create the users table using migrations if you would rather. Note that after you get this error you have to delete the roles and assigned_rolls tables or you will get an error about it not being able to create them.

CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `username` varchar(255) DEFAULT NULL,
    `password` varchar(255) DEFAULT NULL,
    `email` varchar(255) DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL, 
    PRIMARY KEY (`id`)
) CHARSET=utf8 COLLATE=utf8_unicode_ci;

[chorton]

I'm having this same issue. The documentation states that it uses the app/config/auth.php settings and this is what I'm using:

[..] 'model' => 'User', 'table' => 'site_users', [...]

when building the migration scripts, entrust does not properly reference the table name as configured. Confide works well, though.

EntrustSetupTables: alter table assigned_roles add constraint assigned_roles_user_id_foreign foreign key (user_id) references users (id)

it should be using site_users and not users

[stevebauman]

Same issue

[nashultz]

What version of Laravel are you using. If you are using Laravel 4.2, then you will have to change some of the core up a bit due to it now using the core config file (as far as I can tell)

[Zizaco]

For now you will have to change the name of the table in Entrus migration before running it. I'm going to solve this asap.

[dammyammy]

Same Issue

[vanderlin]

Ok so I think I know why this error happens. When creating a foreign key in a table the Key Name must be unique per database. So if you create a Key called assigned_roles_user_id_foreign you can only have one of these. A simple fix is to prefix the foreign key per app, then pass that when creating the many-to-many relationship.

Here is an example.

$foreign_prefix = 'something_unique_per_app'; // global or part of the config..

Schema::table('assigned_roles', function($table) use($foreign_prefix) {
    $table->foreign($foreign_prefix.'user_id')->references('id')->on('users')->onUpdate('cascade')->onDelete('cascade');
    $table->foreign($foreign_prefix.'role_id')->references('id')->on('roles')->onUpdate('cascade')->onDelete('cascade');
});

This runs in to a small problem but easily fixable. You need to update the EntrustRole to know about this prefix.

anytime you call ...where('role_id') or ...where(user_id') you need to prepend the prefix.

example:

  public function beforeDelete($forced = false)
    {
        try {
            DB::table(Config::get('entrust::assigned_roles_table'))->where($foreign_prefix.'role_id', $this->id)->delete();
            DB::table(Config::get('entrust::permission_role_table'))->where($foreign_prefix.'role_id', $this->id)->delete();
        } catch (Exception $e) {
            // do nothing
        }

        return true;
    }

I can make this update, do you think this is a good approach?

[frangeris]

Same issue, Laravel 4.2, any solution?

[oppenheimer]

I fixed the issue by modifying my Users table to make sure the ID field was "unsigned"

[bishalpaudel]

make users table to use innodb as (and it will work perfect):

    Schema::create('users', function ($table) {
        $table->engine = 'InnoDB';

[stephenricks]

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL : alter table sap_assigned_roles add constraint assigned_roles_user_id_fo reign foreign key (user_id) references sap_users (id) on delete casca de on update cascade)

Quote: I fixed the issue by modifying my Users table to make sure the ID field was "unsigned"

this fixed the issue. Have my migration to run without any error.

[muhghazaliakbar]

i have modified my users table.id was unsigned but issue not fix

[michioaida]

For those still having trouble, before you run the 'php artisan migrate' command. Here's how to make it all work: 1) Do you have a users table in existence? If not, create a 'users' table or whatever you name it but remember that name because its important in Step 2. Make sure you create an 'id' column that is unsigned. 2) Go to 'app/database/migrations/' folder. In there you should see a file named something XXXX_XX_XX_XXXXX_entrust_setup_tables.php. Open that file up. Go to line 27 and replace 'user_login' with the name of your users table on Step 1. Save the file and then run 'php artisan migrate'

If you already ran 'php artisan migrate' before following these steps, go to your PHPMyAdmin, or MySQL client software, and drop 'roles' and 'assigned_roles' tables. Just in case, check if 'permissions' or 'permission_role' table exists. If it does, drop them too. Then run the 'php artisan migrate' command.

[tabirkeland]

Looking at the migration that gets created: https://github.com/Zizaco/entrust/blob/master/src/views/generators/migration.blade.php

The $usersTable variable is not being set correctly. Open up the migration and add in the users table name in the role_user schema creation.

[rsoffner]

I solved this by changing the type of ID in the Users table. It is set to 'bigIncrements' in the migration. Change this to Increments, and all works fine.