Foreign key constraint is incorrectly formed in Laravel

user_13237098 0
Viewed 98

I am using Laravel 7 and PHP 7.4.

I'm working on databases and suddenly stuck over issue when I'm trying to generate a foreign key for user in another table. It should be straight away process and I'm following the doc, still getting error.

General error: 1005 Can't create table carchain_qrcode.sellers (errno: 150 "Foreign key constraint is incorrectly formed

User Table

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->bigInteger('id');
        $table->string('name')->unique();
        $table->string('email')->unique();
         });
}

Sellers Table

public function up()
{
    Schema::create('sellers', function (Blueprint $table) {
        $table->bigInteger('id');
        $table->unsignedBigInteger('user_id');
        $table->string('seller_name');
        $table->string('seller_email');

        $table->foreign('user_id')
            ->references('id')->on('users')
            ->onDelete('cascade');

    });

Where have I gone wrong?

laravel mysql php
3 Answers

I think the problem is that the id of your users table is bigInteger and not bigIncrements or unsignedBigInteger.
In Laravel 7 you can simply do: $table->id() to create the id column.

user_34858770

this error occured due to incorrect data type used on the foreign key which you are going to create. The primary key data type on the users table should be same as the data type which you used in Sellers table as below:

Users Table

$table->id();

Sellers Table

$table->unsignedBigInteger('user_id');

or if you used any other data type on users table primary key then the foreign key in the other table should be same accordingly.

user_101834340

Short: For quick fix refer to @Aless55 answer. The main thing to keep in mind is that id and foreign_key column types should match.

Correct way: It is better to use Laravel's built-in APIs for creating primary key and foreign key. More details here: It will automatically take care of column types, creating indexes, and so on. In addition, sticking to official documentation makes it easy to understand the code by others, and maintain it by the developer (It is just my opinion).

In your case:

Users table

public function up()
{
    Schema::create('users', function (Blueprint $table) {
       $table->id();
       $table->string('name')->unique();
       $table->string('email')->unique();
     });
}

Sellers table:

public function up()
{
    Schema::create('sellers', function (Blueprint $table) {
       $table->id();
       $table->foreignId('user_id')->constrained(); // this will automatically detect which table to reference
       $table->string('seller_name');
       $table->string('seller_email');
     });
}
user_12903260
Related