Adding constraints to mysql tables

When we have content in a table that is dependant on another a constraint is required. With Mysql an InnoDB type is required otherwise the constraint is silently ignored. Consider a radios table, models table and a code_plugs table within the radio database.

The constraints would prevent a code_plug or models record being removed if a relative record in the radios table exists.


The create table entries would be:

CREATE TABLE `radios` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `status_id` int(4) NOT NULL DEFAULT '2',
  `model_id` int(4) NOT NULL,
  `serial_number` varchar(40) NOT NULL,
  `software_version` varchar(40) NOT NULL,
  `active` int(4) NOT NULL DEFAULT '1',
  `remarks` varchar(100) NOT NULL,
  `code_plugs_id` int(4) NOT NULL,
  `radio_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial_number` (`serial_number`),
  UNIQUE KEY `radio_id` (`radio_id`),
  CONSTRAINT `FK_code_plugs` FOREIGN KEY (`code_plugs_id`) REFERENCES `code_plugs` (`id`),
  CONSTRAINT `FK_models` FOREIGN KEY (`model_id`) REFERENCES `models` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1376 DEFAULT CHARSET=latin1;


CREATE TABLE `models` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `model` varchar(20) NOT NULL,
  `type` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


CREATE TABLE `code_plugs` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `description` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1112 DEFAULT CHARSET=latin1;



A simple snippet of perl server side code to provide an xml response to a delete attempt would look like:


my $dbh = DBI->connect("dbi:mysql:radio",$dbuser,$dbpass,{RaiseError=>0});
my $id = param("id");
my $table = param("table");

my $rows = $dbh->do("DELETE FROM $table WHERE id = \'$id\';");
my $result;
if($dbh->errstr){
        $result = $dbh->errstr;
        $result =~ s/([\',\`])+//g;
}else{
        $result = $rows;
}

# results are 1 for success and 0E0 for failure
my $xml = '<?xml version="1.0"?>';
$xml .= "\n<options>\n";
$xml .= "<option result=\'$result\' />\n";
$xml .= "</options>";
print "$xml";

A simple javascript handler for the delete may look similar to :


function deleteID(id,table){
        if (confirm("Are you sure?")){
                var request;
                try{
                        request = new XMLHttpRequest();
                }catch(error){
                        try{
                                request = new ActiveXObject("Microsoft.XMLHTTP");
                        }catch(error){
                                return true;
                        }
                }
                request.open('GET', '/cgi-bin/deleteUsingID.pl?id=' + id + '&table=' + table,true);
                request.onreadystatechange = function(){
                        if(request.readyState == 4){
                                var xmlDoc = request.responseXML;
                                var options = xmlDoc.documentElement.getElementsByTagName("option");
                                for (var x = 0; x < options.length; x++){
                                        var result = (options[x].getAttribute("result"));
                                        if (result == '1'){
                                                var rid = 'row_' + id;
                                                var oTable = document.getElementById('content_table');
                                                var oTr = document.getElementById(rid);
                                                oTable.deleteRow(oTr.rowIndex);
                                        }else{
                                                alert(result);
                                        }
                                }
                        }
                }
                request.send(null);
        }
}