Yii integrity constraint exception handling with user friendly message

Question

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

if ($this->loadModel($id)->delete()){...} else {...} should work — Brett Gregson, Oct 15 '12 at 15:06

score 6 · Answer 1 · answered Feb 14 '13 at 14:13

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'
        )
    )
);

score 3 · Accepted Answer · answered Oct 15 '12 at 16:41

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
    }
}

keeg · Answer 3 · 2014-07-08T20:07:58.547

2

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

edited Jul 08 '14 at 20:07

answered Oct 15 '12 at 15:25

keeg

3,990
8
49
97

Yii integrity constraint exception handling with user friendly message

3 Answers3