This works on create/store but not on edit/update. I want the user to be updated and the data already exists in the database, the validation error appears. like this in the user store, I added the data, and it worked, even if there was already the same data then a validation error appeared, but in a different update if I update only the address then the old username data is still used and if I change the username it also works but it doesn't if I replace the username with an existing username the validation error does not appear and instead displays the following error. please help me i am still a student!

Error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'asdf' for key 'user_username_unique' (SQL: update user set username = asdf, password = $2y$10$BYdDToN5jCCuRLdZx70YA.BFgyVIWulL8n/bv5C3VxOVCw6WBN.kO, kota_id = 1505, kecamatan_id = 1505013, desa_id = 1505013004, user.updated_at = 2022-04-09 14:25:03 where id = 4)

User Migration:

Schema::create('user', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('nik')->unique()->nullable();
    $table->string('nama')->nullable();
    $table->string('telp')->unique()->nullable();
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('foto')->nullable();
    $table->string('username')->unique();
    $table->string('password');
    $table->enum('level', ['user','admin'])->default('user');
    $table->unsignedBigInteger('provinsi_id')->nullable();
    $table->unsignedBigInteger('kota_id')->nullable();
    $table->unsignedBigInteger('kecamatan_id')->nullable();
    $table->unsignedBigInteger('desa_id')->nullable();
    $table->text('alamat')->nullable();
    $table->rememberToken();
    $table->timestamps();
});

User Controller Store

public function store(Request $request)
{
    $validasi = $request->validate([
        'username' => ['required', 'string', 'min:3', 'max:30', 'unique:user'],
        'email' => ['required', 'email', 'string', 'max:255', 'unique:user'],
        'password' => ['required', 'string', 'min:8'],
        'nama' => ['required', 'string', 'min:3', 'max:50'],
        'nik' => ['required', 'string', 'min:16', 'max:16', 'unique:user'],
        'telp' => ['required', 'string', 'min:12', 'max:13', 'unique:user'],
        'provinsi_id' => ['required'],
        'kota_id' => ['required'],
        'kecamatan_id' => ['required'],
        'desa_id' => ['required'],
        'foto' => ['mimes:jpeg,jpg,png'],
        'level' => ['required'],
        'alamat' => ['required'],
        ]);
    $validasi['password'] = Hash::make('password');
    $create = User::create($validasi);
    if($request->hasFile('foto')){
        $request->file('foto')->move('images/',$request->file('foto')->getClientOriginalName());
        $create->foto = $request->file('foto')->getClientOriginalName();
        $create->save();
    }

    return redirect()->route('user.index');
}

User Controller Update

public function update(Request $request, $id)
{
    $user = User::find($id);
    $validasi = $request->validate([
        'username' => ['required', 'string', 'min:3', 'max:30', 'unique:user,id'],
        'email' => ['required', 'email', 'string', 'max:255', 'unique:user,id'],
        'password' => ['required', 'string', 'min:8', 'max:20'],
        'nama' => ['required', 'string', 'min:3', 'max:50'],
        'nik' => ['required', 'string', 'min:16', 'max:16', 'unique:user,id'],
        'telp' => ['required', 'string', 'min:12', 'max:13', 'unique:user,id'],
        'provinsi_id' => ['required'],
        'kota_id' => ['required'],
        'kecamatan_id' => ['required'],
        'desa_id' => ['required'],
        'foto' => ['mimes:jpeg,jpg,png'],
        'level' => ['required'],
        'alamat' => ['required'],
    ]);
    $validasi['password'] = Hash::make('password');
    $user->update($validasi);
    if($request->hasFile('foto')){
        $request->file('foto')->move('images/',$request->file('foto')->getClientOriginalName());
        $user->foto = $request->file('foto')->getClientOriginalName();
        $user->save();
     }

    return redirect()->route('user.index');
}

CodePudding user response：

I think what you're looking for is to have a rule in place that "excludes" the current user to be checked against the uniqueness of the properties it owns already: https://laravel.com/docs/5.8/validation#rule-unique

CodePudding user response：

The Cause

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'asdf' for key 'user_username_unique' (SQL: update `user` set `username` = asdf, `password` = ...)

The error above thrown by the SQL Database saying that you are trying to update a record but the username is already exists and used by another record.

This is why I ask for clarification, "how much you know about sql unique constraint?"

You could somewhat avoid the error from arising by validating the user input using Laravel Validation using unique rule:

$request->validate(['username' => [..., 'unique:user'], ...]);

You already have one and a perfect working sample on your store function. The declaration above told Laravel to check whether we have existing username in user table or not.

However, your update function does not validate username field as you have declared them:

 $request->validate(['username' => [..., 'unique:user,id'],...]);

The declaration above means "check if username already exists on user table on id column." Laravel then checks username on id column. Hence, Laravel thought that username doesn't exists on the table. Fortunately, you have declared on your migration to ensure username to be unique - and thats why you have that particular error.

The solution

You can either exclude the username from edit procedure or update your validation to exclude the used username from firing validation error.

$validasi = $request->validate([
            'username' => ['required', 'string', 'min:3', 'max:30',
                           Rule::unique('user')->ignore($user->id)], ...]);

Rule::unique('user')->ignore($user->id) means Laravel must ensure the username is not used by no record other than with given primary key $user->id.

Replacing 'unique:user,id' with Rule::unique('user')->ignore($user->id) will ensure that the username is unique against all the other records except the current one. This is what Andreas Bilz meant with his link to Laravel documentation.

Note:

Ellipsis (...) are to shorten the statements
You should keep watch for SQL Error should your application used by multiple users and consider transaction whenever atomicity become mandatory (see: transaction)
Be kind to people trying to help