laravel 8 foreign key

Question

I try to migrate my table that have a foreign keys. Every time I migrate my table it produce an error which is saying:

General error: 1215 Cannot add foreign key constraint

Here is my table migration:

Schema::create('profile_pictures', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('user_id')->nullable();
    $table->binary('image')->nullable();
    $table->timestamps();

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

And here is my model:

class ProfilePicture extends Model
{
    protected $fillable = [
        'user_id',
        'image'
    ];

    public function user()
    {
        $this->belongsTo(User::class, 'user_id', 'id');
    }
}

Here is my user table migration:

Schema::create('users', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('username');
    $table->string('first_name')->nullable();
    $table->string('last_name')->nullable();
    $table->string('email')->unique();
    $table->string('phone')->nullable();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

Solution 1

Update the user_id col from bigInteger to UnsignedBigInteger as you need the same datatype and length for PK and FK.

Schema::create('profile_pictures', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('user_id');
    $table->binary('image')->nullable();
    $table->timestamps();

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

I will suggest to follow convention and use foreignId() method with constrained()

Sample(from documentation):

Schema::table('posts', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
});

You can get more details here: https://laravel.com/docs/8.x/migrations#foreign-key-constraints

Solution 2

According to WL#148, a foreign key column must have the same data type + the same length + the same scale as the corresponding referenced column.

I think you should use

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

Instead of

$table->bigInteger('user_id')->nullable();

Comments

[lagbox]

we would need to see the migration for the users table as well just to check the 'id' field type

[Shariffuddin Hussin]

I already put user migration table

[porloscerros Ψ]

Try $table->unsignedBigInteger('user_id')->nullable();

[Shariffuddin Hussin]

the error already gone but the user_id is empty

[porloscerros Ψ]

What do you mean that user_id is empty? Migrations are only to create the DB tables, not to insert data.

[Shariffuddin Hussin]

the user_id stay nullable if I update new picture on the table