Impossible to create my foreign key (1:n) error 1215

Question

I try to create a foreign key between tables users and roles (a user can have a role, a role can be given to several users)

I created first all my migrations, and then, I added a file with the foreign keys, to be sure that all tables exist.

The migrations :

Like you can, see the last migration is about foreign keys, this is the code :

class CreateForeignKeys extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->unsignedBigInteger('role_id');

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

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

This is the code of my user migration :

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('nom', 45);
            $table->string('prenom', 45);
            $table->string('team', 45)->nullable();
            $table->string('tel1', 45)->nullable();
            $table->string('tel2', 45)->nullable();
            $table->string('username', 50)->nullable();
            $table->string('password', 50)->nullable();
            $table->tinyInteger('is_active');
            $table->dateTime('created')->nullable();
            $table->dateTime('modified')->nullable();[![enter image description here][2]][2]
        });
    }

This is my database structure, like you can, I have the field "role_id", but not the key showing the foreign key and I have also this error :

And the error :

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: create table roles (id int unsigned not null auto_increment primary key, name varchar(100) null, description varchar(255) null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

Tapez un message

Thank you for your help.

Are all tables empty? Otherwise there might be some corrupt data. Or the two fields between which you are trying to add a foreign key are not of the same type. — Jules

Mike Ross Mike Ross · Accepted Answer · 2020-04-25T14:54:59

You are using

$table->unsignedBigInteger('role_id');

Make sure column type matches on roles table id like

$table->bigIncrements('id');

Or change it to

$table->integer('role_id');

Hope this helps

Impossible to create my foreign key (1:n) error 1215

2 Answers