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