CODEWITHSUNDEEP
yii2
Dark Cyber
Dark Cyber

Reputation: 2231

How to handle Integrity constraint violation on delete?

I have rows, which some rows can't be delete because it referenced to other table, and the other can be delete.

What I want is delete rows which can be delete and leave the other rows which can't be delete

so far my code is 

$tkota = TbKota::find()->all();
            foreach($tkota as $kota){
                if($kota->delete()){
                    echo "del success<br/>";
                }else{
                    echo "fail ".$kota['api_id']."<br/>";
                }
            }

my above code produce this error 

SQLSTATE[23503]: Foreign key violation: 7 ERROR: update or delete on table "tb_kota" violates foreign key constraint "fk_tb_produ_reference_tb_kota" on table "tb_produk_ekspedisi_by_vendor"
    DETAIL: Key (kota_id)=(1771) is still referenced from table "tb_produk_ekspedisi_by_vendor".
    The SQL being executed was: DELETE FROM "tb_kota" WHERE "kota_id"=1771

instead of show success when record deleted and show fail if record can't be delete.

what's wrong with my code?

thanks in advance.

Upvotes: 2

Views: 2805

Answers (2)

Yasar Arafath
Yasar Arafath

Reputation: 625

This one will be better

use yii\db\IntegrityException;

use yii\web\NotFoundHttpException;

  foreach($tkota as $kota){

  $connection = \Yii::$app->db;
  $transaction = $connection->beginTransaction();

  try {
          $kota->delete();
          $transaction->commit();
          return $this->redirect(['user/view', 'id' => $model->id]);

      }catch (IntegrityException $e) {

           $transaction->rollBack();
           throw new \yii\web\HttpException(500,"YOUR MESSAGE.", 405);


      }catch (\Exception $e) {

          $transaction->rollBack();
          throw new \yii\web\HttpException(500,"YOUR MESSAGE", 405);


    }

}

Upvotes: 2

Insane Skull
Insane Skull

Reputation: 9368

foreach($tkota as $kota){
     try {
        if($kota->delete()){
           echo "del success<br/>";
        }
     } catch (\Exception $e) {
        echo "fail ".$kota['api_id']."<br/>";
     }
}

Upvotes: 1

Related Questions