PHP and Foreign Key Constraints

I hate having a Really Big Database that complains when you do a DELETE FROM table_foo with something obscure like Cannot delete or update a parent row: a foreign key constraint fails.

Great, mysql, just $##%ing wonderful. There's 17 tables that reference table_foo, which one is broken?

I think to myself: surely there's something that will show me all of the keys pointing to this table, yeah?

No. Not even a hint. I was stuck using DB_Visualiser and inspecting tables. Ugh!

Below is a solution I bothered to whip up at work today. $db is an instance of PEAR::DB, CLS_html::Form_Start() is merely a FORM tag with some extras. Ditto CLS_html::input(). By default, PEAR::DB fetchs row names (Fetch Mode Assoc?) rather than numbers.

<?php

/**
* What tables reference $target?
*/
function references($target, $keys) {
 $matches = array();

 foreach ($keys as $key => $tgt) {
     if ($target == $tgt) {
         $matches[] = $key;
     }
 }

 return $matches;
}

function findKeys() {
 global $db;

 $pattern = "/CONSTRAINT `.*` FOREIGN KEY \(`(.*)`\) REFERENCES `(.*)` \(`(.*)`\)/i";

 $sql = "SHOW TABLES";
 $result = $db->query($sql);

 $matches = array();
 $keys = array();

 while ($row = $result->fetchRow()) {
     foreach ($row as $table) {
         $syntax = $db->query(sprintf("SHOW CREATE TABLE %s", $table))->fetchRow();

         preg_match_all($pattern, $syntax["Create Table"], $matches);

         for ($i = 0; $i < count($matches[0]); $i++) {
             $keys[$syntax["Table"] . "." . $matches[1][$i]] = $matches[2][$i] .
                                                         "." . $matches[3][$i];
         }
     }
 }

 return $keys;
}

if (!isset($_REQUEST["field"]) || !isset($_REQUEST["value"])) {
 print CLS_html::Form_Start();

 print '<p>';
 print 'Field<br />';
 print CLS_html::input("text","field");
 print 'Value<br />';
 print CLS_html::input("text","value");
 print CLS_html::input("submit","","Describe");
 print '</p>';

 print CLS_html::Form_End();
} else {
 $keys = findKeys();
 $references = references($_REQUEST["field"],$keys);
 $args = $_REQUEST["value"];


 print '<pre>';

 for ($i=0;$i<count($references); $i++) {
     $parts = explode(".", $references[$i]);
     $sql = sprintf("SELECT * FROM %s
                      WHERE %s = ? LIMIT 1",
                   $parts[0],
                   $references[$i]);
     $result = $db->query($sql, $args);

  
     if ($result->numRows() > 0) {
         print str_replace("?",$_REQUEST["value"],$sql) . ";\n";
     }
 }
 print '</pre>';
}




?>