Laravel check for constraint violation

Question

I was curious if there is a way we can check if there is a constraint violation error when delete or insert a record into the database.

The exception thrown is called 'QueryException' but this can be a wide range of errors. Would be nice if we can check in the exception what the specific error is.

Answer 1

You are looking for the 23000 Error code (Integrity Constraint Violation). If you take a look at QueryException class, it extends from PDOException, so you can access to $errorInfo variable.

To catch this error, you may try:

try {
  // ...

} catch (\Illuminate\Database\QueryException $e) {
    var_dump($e->errorInfo);
}

// Example output from MySQL
array (size=3)
   0 => string '23000' (length=5)
   1 => int 1452
   2 => string 'Cannot add or update a child row: a foreign key constraint fails (...)'

To be more specific (Duplicate entry, not null, add/update child row, delete parent row...), it depends on each DBMS:

• PostgreSQL and SQL server follow the SQL standard's conventions for SQLSTATE code, so you may return the first value from the array $e->errorInfo[0] or call $e->getCode() directly
• MySQL, MariaDB and SQLite do not strictly obey the rules, so you need to return the second value from the array $e->errorInfo[1]

For laravel, handling errors is easy, just add this code in your "app/start/global.php" file ( or create a service provider):

App::error(function(\Illuminate\Database\QueryException $exception)
{
    $error = $exception->errorInfo;
    // add your business logic
});

Answer 2

first put this in your controller

use Exception;

second handle the error by using try catch like this example

try{    //here trying to update email and phone in db which are unique values
        DB::table('users')
            ->where('role_id',1)
            ->update($edit);
        return redirect("admin/update_profile")
               ->with('update','update');
            }catch(Exception $e){
             //if email or phone exist before in db redirect with error messages
                return redirect()->back()->with('phone_email','phone_email_exist before');
            }

New updates here without need to use try catch you can easily do that in validation rules as the following code blew

public function update(Request $request, $id)
{
    $profile = request()->all();
    $rules    = [
            'name'                       => 'required|unique:users,id,'.$id,
            'email'                      => 'required|email|unique:users,id,'.$id,
            'phone'                      => 'required|unique:users,id,'.$id,
    ];
    $validator = Validator::make($profile,$rules);
    if ($validator->fails()){
        return redirect()->back()->withInput($profile)->withErrors($validator);
    }else{
        if(!empty($profile['password'])){
            $save['password'] = bcrypt($profile['password']);
        }
        $save['name']                  = $profile['name'];
        $save['email']                 = $profile['email'];
        $save['phone']                 = $profile['phone'];
        $save['remember_token']        = $profile['_token'];
        $save['updated_at']            = Carbon::now();

        DB::table('users')->where('id',$id)->update($save);
        return redirect()->back()->with('update','update');
    }
}

where id related to record which you edit.

Answer 3

You may also try

try {
       ...
    } catch ( \Exception $e) {
         var_dump($e->errorInfo );
    }

then look for error code.

This catches all exception including QueryException

Answer 4

If you are using Laravel version 5 and want global exception handling of specific cases you should put your code in the report method of the /app/Exception/Handler.php file. Here is an example of how we do it in one of our micro services:

public function render($request, Exception $e)
{
    $response   = app()->make(\App\Support\Response::class);
    $details = $this->details($e);

    $shouldRenderHttp = $details['statusCode'] >= 500 && config('app.env') !== 'production';
    if($shouldRenderHttp) {
        return parent::render($request, $e);
    }

    return $response->setStatusCode($details['statusCode'])->withMessage($details['message']);
}

protected function details(Exception $e) : array
{
    // We will give Error 500 if we cannot detect the error from the exception
    $statusCode = 500;
    $message = $e->getMessage();

    if (method_exists($e, 'getStatusCode')) { // Not all Exceptions have a http status code
        $statusCode = $e->getStatusCode();
    } 

    if($e instanceof ModelNotFoundException) {
        $statusCode = 404;
    }
    else if($e instanceof QueryException) {
        $statusCode = 406;
        $integrityConstraintViolation = 1451;
        if ($e->errorInfo[1] == $integrityConstraintViolation) {
            $message = "Cannot proceed with query, it is referenced by other records in the database.";
            \Log::info($e->errorInfo[2]);
        }
        else {
            $message = 'Could not execute query: ' . $e->errorInfo[2];
            \Log::error($message);
        }
    }
    elseif ($e instanceof NotFoundHttpException) {
        $message = "Url does not exist.";
    }

    return compact('statusCode', 'message');
}

The Response class we use is a simple wrapper of Symfony\Component\HttpFoundation\Response as HttpResponse which returns HTTP responses in a way that better suits us.

Have a look at the documentation, it is straightforward.