Why can't I properly create a table with a foreign key?

Question

I want to create 2 tables:

1. A table called roles
2. And a table called users

In users I want to have a column called role_id and it should be a reference to roles table.

This is how roles table should look like:

So here is a migration for user table:

Schema::create('users', function (Blueprint $table) {
    $table->id();

    $table->bigInteger('role_id')->unsigned();
    $table->foreign('role_id')->references('role_id')->on('roles');

    $table->timestamps();
});

And here is a migration for roles talbe:

Schema::create('roles', function (Blueprint $table) {
    $table->id();
    $table->string('name');
});

The error I get when I run php artisan migrate

SQLSTATE[HY000]: General error: 1005 Can't create table my_db.users (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table users add constraint users_role_id_foreign foreign key (role_id) references roles (role_id))

Answer 1

Change references('role_id') in references('id') which means that references in column 'id' in roles table:

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

Also you should create first roles table and after that users table. Just change the dates on database/migrations like this (example):

2020_09_11_150331_create_roles_table.php 2020_09_12_000000_create_users_table.php

run:

php artisan migrate

This way first will be created the roles table and then users table which will handle a foreign key which references 'id' column in roles table.

Answer 2

There is an definition problem make it like $table->foreign('role_id')->references('id')->on('roles');

or you need to make primary key of roles table as role_id i guess this will resolve what you are looking for