Laravel 5.8: General error: 1005 Can't create table

Question

I'm working with Laravel 5.8 to develop my project, and I just create a Migration goes like this:

public function up()
    {
        Schema::create('user_wallet_transactions', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedSmallInteger('user_id');
            $table->foreign('user_id')->references('usr_id')->on('users');
            $table->unsignedSmallInteger('wallet_id');
            $table->foreign('wallet_id')->references('id')->on('wallets');
            $table->string('amount');
            $table->string('description');
            $table->timestamps();
        });
    }

But when I run this I get this error:

SQLSTATE[HY000]: General error: 1005 Can't create table `nanonew_main`.`user_wallet_transactions` (errno: 150 "Foreign key
 constraint is incorrectly formed") (SQL: alter table `user_wallet_transactions` add constraint `user_wallet_transactions_user_id_foreign` foreign key (`user_id`)
references `users` (`usr_id`))

I don't know what is going wrong here! My table users goes like this and have the field named usr_id:

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('usr_id');
            $table->string('usr_name')->unique();
            $table->boolean('usr_is_admin')->default(0)->comment('0 = admin , 1 = not admin');
            $table->boolean('usr_is_active')->default(0);
            $table->string('usr_email')->unique()->nullable();
            $table->timestamp('usr_email_verified_at')->nullable();
            $table->string('usr_password');
            $table->rememberToken();
            $table->timestamps();

            $table->engine = 'InnoDB';
        });
    }

And table wallets also have the field named id:

public function up()
    {
        Schema::create('wallets', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->string('name')->unique();
            $table->tinyInteger('is_active');
            $table->tinyInteger('is_cashable');
            $table->timestamps();
        });
    }

So what's the issue here? How can I fix it?

I would really appreciate any idea or suggestion from you guys...

Thanks in advance.

First you need to migrate `users` and `wallets` table and then `user_wallet_transactions`. — STA, Jul 11 '21 at 10:01
The columns need to be of the same type, `bigIncrements()` creates a "UNSIGNED BIGINT" column, not "unsignedSmallInteger". https://laravel.com/docs/5.8/migrations#columns — brombeer, Jul 11 '21 at 10:03
@brombeer I changed `$table->unsignedSmallInteger('user_id');` & `$table->unsignedSmallInteger('wallet_id');` to ` $table->bigIncrements('user_id');` & `$table->bigIncrements('wallet_id');` but didn't solve the problem — , Jul 11 '21 at 10:05
It would be `unsignedBigInteger` ex : `$table->unsignedBigInteger('user_id');` and `$table->unsignedBigInteger('wallet_id');` this can solve your problem — STA, Jul 11 '21 at 10:06
You'd need `unsignedBigInteger`, not `bigIncrements`. And it's `usr_id`, not `user_id` — brombeer, Jul 11 '21 at 10:06
Another thing, looks like you got `'0 = admin , 1 = not admin'` mixed up for your `boolean('usr_is_admin')` — brombeer, Jul 11 '21 at 10:11
Any specific reason as to why all your columns are prefixed with `usr_` in your `users` table? — brombeer, Jul 11 '21 at 10:13

score 1 · Accepted Answer · answered Jul 11 '21 at 10:14

You are using bigIncrements for primary key, so make your foreign key also the same type, change :

$table->unsignedSmallInteger('user_id');
$table->unsignedSmallInteger('wallet_id');

to

$table->unsignedBigInteger('user_id');
$table->unsignedBigInteger('wallet_id');

Or

$table->bigInteger('user_id')->unsigned();
$table->bigInteger('wallet_id')->unsigned();

Laravel 5.8: General error: 1005 Can't create table

1 Answers1