CODEWITHSUNDEEP
phpmysqlangularjs
tanyaa
tanyaa

Reputation: 155

cannot delete a database row because it has foreign key

I'm building a project with Angular and PHP, I have a "Customers" table from which I can retrieve all data, but I have a problem with deleting one row. if the "Customer" has "Orders", I can't delete the (row)"Customer". if the "Customer" doesn't have "orders", I can delete with no problem. This is the error that I get on phpmyadmin

=#1451 - Cannot delete or update a parent row: a foreign key constraint fails.

Can anyone help?

PHP code for deleting:

<?php
 header('Content-Type: text/html; charset=utf-8');
$connect=mysqli_connect("localhost", "root", "", "hamatkin");

  include_once 'Customer.php';
mysqli_query($connect,"SET character_set_client = utf8");
mysqli_query($connect,"SET character_set_connection = utf8");
mysqli_query($connect,"SET character_set_results = utf8");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$data = json_decode(file_get_contents("php://input"));
$x=$data->customer_id;
 $customer_id = $data->customer_id;
$del = "DELETE FROM customers WHERE customer_id=".$customer_id;
   mysqli_query($connect, $del);
 }
$newURL = "/hamatkin/#/customerCards";
header('Location: '.$newURL);
?>

controller:

  $scope.delete = function(deletingId, $index) {

       var params = $.param({"customer_id":deletingId});
      $http.post('api/customers-tab/delete-customer.php',{"customer_id":deletingId})
          .success(function(data){
           var arr=JSON.parse(JSON.stringify(data));
            $scope.customerDetails = arr;
             var arr2 = arr.split(",");
              arr2.splice($index, 1);
              $route.reload();
        });
      }

html code:

<tr ng-repeat="x in customers | filter:search_query | orderBy: order_query:reverse_query">
            
            <td>{{ x.customer_id}}</td>
            <td>{{ x.kind_Of_Customer}}</td>
            <td>{{ x.full_name}}</td>                <td> {{ x.id}} </td>
            <td> {{ x.city}} </td>
            
            <td><a href="/hamatkin/index.html#/customerCardDetails/{{ x.customer_id}}"  class="btn btn-primary btn- active">הצג פרטי לקוח </a></td>
          
            <td><a ng-click="delete(x.customer_id, $index)"  class="btn btn-primary btn- active">מחיקה</td>

Upvotes: 0

Views: 1134

Answers (2)

O. Jones
O. Jones

Reputation: 108816

Your schema is rigged with a constraint requiring you to delete all a customer's orders before deleting the customer. As long as any orders remain for a customer, MySQL won't let you delete the customer.

What is the business meaning of deleting a customer row? Many customer tracking systems don't delete rows like that; instead they add an active column and set it to zero when a customer is no longer active. Actually deleting a customer with an order history can cause confusion when you're trying to reconcile stuff, at the end of the year maybe.

If you're cleaning up a development or test database, just delete the order rows before deleting the customer row.

If you're at a recent MySQL version, you can specify ON DELETE CASCADE in the definition of your constraint, as shown here. But this is a sketchy way to proceed.

Upvotes: 3

Rani Radcliff
Rani Radcliff

Reputation: 5076

You could add a CascadeDelete to your table so that when a parent record is deleted it's children are deleted as well. You could also write code to use the key field from the selected Customer to delete the orders in the background first, and then delete the Customer. But Angular is not going to override your database constraints.

Upvotes: 2

Related Questions