SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0430142821' for key 'users_melli_unique'

Question

I know this question asked before but i want to know how can i stop to showing this error and instead of this i want to show custom error message to user in blade.

This is user migration :

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('image')->nullable();
        $table->string('email')->unique();
        $table->boolean('is_superuser')->default(0);
        $table->boolean('is_staff')->default(0);
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->string('phone_number')->nullable()->unique();
        $table->enum('two_factor_type', ['off', 'sms']);
        $table->string('melli')->nullable()->unique();
        $table->rememberToken();
        $table->timestamps();
    });
}

UPDATE: This is my insert function :

$user = User::find(auth()->user()->id);
$request->validate([
    'melli' => 'min:10|max:10',
    'cart' => 'min:16|max:16'
]);;
$user->update([
    'name' => $request['name'],
    'cart' => $request['cart'],
    'melli' => $request['melli'],
    'gender' => $request['gender']
]);
return redirect(route('profile.index'));

This error happens when i have a melli in my database like 1234567890 and i try to write 1234567890 again, because the melli field is unique.

You don't show your insert query, but I'd suggest using a try/catch block, or look for an existing Melli string before inserting. — aynber
Normally you would handle such state during validation, and return custom messages to blade. If you want to handle it yourself, then as @aynber suggested, catch the exception and return your own message to plade — user3532758
Use unique validation in melli field. laravel.com/docs/7.x/validation#rule-unique check this as well to handle update stackoverflow.com/a/48492198/12459934 — Avi

mare96 mare96 · Accepted Answer · 2020-09-01T14:21:31

You need unique validation rule and ignore that id in the update.

You can do like this:

$request->validate([
   'melli' => 'min:10|max:10|unique:users,melli,' .$user->id,
]);

Or

$request->validate([
    'melli' => [
        'min:10',
        'max:10',
        Rule::unique('users')->ignore($user->id, 'id')
    ],
    'cart' => 'min:16|max:16'
]);

I think that's what you need.

Also, some helpful answer.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0430142821' for key 'users_melli_unique'

1 Answers