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);
}
}
