Delete Row From Table

I have two tables in mysql database with foreign relation. When I try to delete a row from table I get an following database exception:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`cms`.`articles`, CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`category`) REFERENCES `categories` (`id`))

The SQL being executed was: DELETE FROM `categories` WHERE `id`=5

which is fine, because articles table has a relation with category table, the problem is that I can’t catch that exception to put a custom error.

This is my action from controller with test error:

public function actionDelete($id)

{

    try {

        $this->findModel($id)->delete();

        return $this->redirect(['index']);

    } catch(CDbException $e) {

        echo 'Please remove this category from articles to delete this category.';

    }

}

It is rights because that error is during executing delete SQL.

You have 2 solutuons:

• try to use transaction, inserting your delete call in a transaction;

• check before delete that not exists categories referenced at that article.

I’m not sure if it matters but CDbException is from Yii 1.1x I think you should use Exception.

Hope it helps

Thanks for replay. I tried using transaction but with no luck, it doesn’t catch exception. This is my new action:

public function actionDelete($id)

    {

        $connection = Yii::$app->db;

        $transaction = $connection->beginTransaction();

        try {

            $connection->createCommand()->delete('categories', ['id' => $id])->execute();

            $transaction->commit();

            

            return $this->redirect(['index']);

            

        } catch (Exception $e) {

            $transaction->rollBack();

        }

    }