cannot delete a database row because it has foreign key

Question

I'm building a project with angular and php, I have a "Customers" table wich 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" dosn'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>

help with what? you're trying to do something that violates a constraint in your database. either remove the constraint, or obey it. And note that you're vulnerable to [sql injection attacks](http://bobby-tables.com) — Marc B, Jul 27 '16 at 20:43
"nothing else you can do" is not what I would have taken from Marcs comment. Your options are to do nothing, or remove the foreign key restraint from that table. — castis, Jul 27 '16 at 20:48
First you need to check if the Customer haves orders...if haves order delete all the orders, then the Customer.... — Hackerman, Jul 27 '16 at 20:48
Possible duplicate of [Basics of Foreign Keys in MySQL?](http://stackoverflow.com/questions/757181/basics-of-foreign-keys-in-mysql) — Martin, Jul 27 '16 at 20:50
Also useful question to read is http://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete — Martin, Jul 27 '16 at 20:50
@tanyaa a more viable solution is to never delete records from your database, and instead have an "active" column or an "is_deleted" column, so when you need to delete something you can mark active=0 and is_deleted=1. — Charles D Pantoga, Jul 27 '16 at 20:54
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put unescaped user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Jul 27 '16 at 21:01
@Hackman how can I check if the customer has orders in code? — tanyaa, Jul 27 '16 at 21:01

score 3 · Answer 1 · answered Jul 27 '16 at 20:54

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.

score 2 · Answer 2 · answered Jul 27 '16 at 20:49

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.

cannot delete a database row because it has foreign key

2 Answers2