Integrity constraint violation when using a Foreign key for a user role

Question

I'm trying to define a role for my users using a "role_id" foreign key which referes to the "id" of my "roles" table.

The migrations worked well but when I'm trying to register I get an error.

Migration users

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->char('nni', 6);
            $table->string('firstname');
            $table->string('lastname');
            $table->string('email')->unique();
            $table->unsignedBigInteger('role_id')->default(1);
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();

            $table->foreign('role_id')->references('id')->on('roles');
        });
    }

Models/User

class User extends Authenticatable
{
    use Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'nni', 'firstname', 'lastname', 'email', 'role_id', 'admin', 'password',
    ];

[...]

public function role()
    {
        return $this->belongsTo(Role::class);
    }
}

Migration roles

class CreateRolesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('roles', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('description');
            $table->timestamps();
        });
    }

[...]

}

The error

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update >a child row: a foreign key constraint fails (projectsms.users, >CONSTRAINT users_role_id_foreign FOREIGN KEY (role_id) REFERENCES >roles (id))

If you know where is my problem let me know !

note the migration order. if the users table is being created before the roles table, it will fail the constraint when it tries to create. foreign tables should be created first before trying to reference them. — Erich
The value of the role_id doesnt reference an entry in your roles table. Is there a role id = 1 in your roles table ? $table->unsignedBigInteger('role_id')->default(1); — N69S
Tried, and got this : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table users` add constraint users_role_id_foreign foreign key (role_id) references roles (id))` — Petoux
@N69S I have no role_id = 1 in my roles table, why would I have it? — Petoux
@Petoux because you set the default value of role_id in the table users to 1. If you have users without roles, set the field as nullable $table->unsignedBigInteger('role_id')->nullable(); — N69S

N69S N69S · Accepted Answer · 2019-08-23T13:14:11

Since your structure depends on the fact that every use must have a role, you should include the insertion of the default role in your migration.

class CreateRolesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('roles', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('description');
            $table->timestamps();
        });

        DB::table('roles')->insert([
            'id' => 1, //must be 1
            'name' => 'default',
            'description' => 'default role (just registered)',
            'created_at' => \Carbon\Carbon::now(),
            'updated_at' => \Carbon\Carbon::now(),
        ]);
    }

[...]

}

Integrity constraint violation when using a Foreign key for a user role

1 Answers