CODEWITHSUNDEEP
exceptionerror-handlingreferential-integrityyii
ali asad
ali asad

Reputation: 1349

Yii integrity constraint exception handling with user friendly message

I am using this model code to delete a record.

public function actionDelete($id)
{
        $this->loadModel($id)->delete();

        // if AJAX request (triggered by deletion via admin grid view), we should not redirect the browser
        if(!isset($_GET['ajax']))
            $this->redirect(isset($_POST['returnUrl']) ? $_POST['returnUrl'] : array('admin'));
}

The table containing this record has one to many relationship with other table with on delete restrict constraint.

So when deleting a record which has related records in child table it throws exception like

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`bzuexamsystem`.`campus`, CONSTRAINT `fk_Campus_City` FOREIGN KEY (`CityID`) REFERENCES `city` (`CityID`) ON UPDATE CASCADE). The SQL statement executed was: DELETE FROM `city` WHERE `city`.`CityID`=1

Is there someway to show user friendly error message. Thanks

Upvotes: 0

Views: 5597

Answers (3)

keeg
keeg

Reputation: 3978

You cannot delete rows that have restrict on the foreign key, change that to set to null, or no action depending on your requirements

so your key would be set to null on delete and cascade on update

Upvotes: 2

Kirill Funtov
Kirill Funtov

Reputation: 71

You need to catch exception. Something like 

try{
    $this->loadModel($id)->delete();
    if(!isset($_GET['ajax']))
            $this->redirect(isset($_POST['returnUrl']) ? $_POST['returnUrl'] : array('admin'));
    }catch (CDbException $e){
        if($e->getCode()===23000){
            //You can have nother error handling
            header("HTTP/1.0 400 Relation Restriction");
        }else{
            throw $e;
        }
    }

If you also use CGrigView in your view file, you should pass "ajaxUpdateError" function to it. Example:

$this->widget('zii.widgets.grid.CGridView',
  array(
    'id' => 'model-grid',
    'dataProvider' => $model->search(),
    'filter' => $model,
    'ajaxUpdateError' => <<<JS
      function(xhr, ts, et, err){
        if(xhr.statusText==="Relation Restriction"){
          $("#errorDiv").text("That model is used by something!");
        }
        else{
          alert(err);
        }
      }
    JS
    ,
    'columns' => 
        array(
            'model_id',
            'name'
        )
    )
);

Upvotes: 6

icefront
icefront

Reputation: 174

I guess, $this->loadModel() returns a CActiveRecord object...

First at all you need to make sure the record you want to delete is really loaded. Second, use @ at the start of the statement do disallow errors. Then if the CActiveRecord->delete() returns false, it means, that the record wasn't deleted:

public function actionDelete($id) {
    $record = $this->loadModel($id);
    if ($record !== null) {
        if (@$record->delete()) {
            // code when successfully deleted
        } else {
            // code when delete fails
        }
    } else {
        // optional code to handle "record isn't found" case
    }
}

Upvotes: 3

Related Questions