CODEWITHSUNDEEP
mysqllaravellaravel-migrations
Tyoma Inagamov
Tyoma Inagamov

Reputation: 125

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

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:

Roles table

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))

Upvotes: 1

Views: 333

Answers (2)

Marinario Agalliu
Marinario Agalliu

Reputation: 1179

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.

Upvotes: 3

Divyesh pal
Divyesh pal

Reputation: 947

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

Upvotes: 3

Related Questions