CODEWITHSUNDEEP
phpmysqllaravel
Shaan
Shaan

Reputation: 483

Foreign key constraint is incorrectly formed in Laravel

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?

Upvotes: 2

Views: 115

Answers (3)

Aless55
Aless55

Reputation: 2709

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.

Upvotes: 6

kigibek
kigibek

Reputation: 611

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');
     });
}

Upvotes: 4

Naveed Ali
Naveed Ali

Reputation: 1045

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.

Upvotes: 4

Related Questions