Checking for foreign key restrictions errors without mysqli_report

Question

For the following comment (source)

PHP does not report mysqli or PDO errors by default because that information is highly sensitive, displaying it to a user is a great way to learn how to inject malicious data.

MYSQLI_REPORT_ERROR tells it to turn on the errors and MYSQLI_REPORT_STRICT tells it to convert those errors into Exceptions. This will give you a full report of the error message, so never do this in production environments.

How would one check for foreign key constraint errors in production to notify the user that he or she is, for example, not able to delete a record until all associated records has been deleted?

Answer 1

As a general guideline, exceptions should not be used for control flow. What you should rather do is guard your DELETE statements inside if statements, i.e.:

<?php

$mysqli = new mysqli(/* blah */);
$parent_id = /* id of parent record that we're trying to delete */;

if ($stmt = $mysqli->prepare("select count(1) from child_table where parent_id = ?")) {
    $stmt->bind_param("i", $parent_id);
    $stmt->execute();
    $stmt->bind_result($child_row_count);
    $stmt->fetch();
    $stmt->close();

    if ($child_row_count == 0) {
        // there is no dependant object, so we can delete
    }
    else {
        die("Can't delete object, it has child objects linked to it");
    }
}

?>

An alternative would be to use cascading deletes to automatically remove child data, in which case you no longer need to check for orphans.