Hi,
I've always worked with MyISAM but thought I'd give InnoDB a try 😃
By following some tutorials and other reference materials I cam up with the following DB;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE IF NOT EXISTS `images` (
`iID` int(10) NOT NULL auto_increment,
`sID` int(10) NOT NULL,
`description` varchar(255) collate utf8_unicode_ci NOT NULL,
`filename` varchar(255) collate utf8_unicode_ci NOT NULL,
`price` decimal(10,2) default NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`iID`),
KEY `sID` (`sID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `series` (
`sID` int(10) NOT NULL auto_increment,
`description` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`sID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `tags` (
`tID` int(10) NOT NULL auto_increment,
`ttID` int(10) NOT NULL,
`description` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`tID`),
UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `tags2img` (
`tID` int(10) NOT NULL,
`iID` int(10) NOT NULL,
KEY `iID` (`iID`),
KEY `tID` (`tID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `tag_types` (
`ttID` int(10) NOT NULL auto_increment,
`description` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`ttID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
ALTER TABLE `images`
ADD CONSTRAINT `images_ibfk_2` FOREIGN KEY (`sID`) REFERENCES `series` (`sID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `tags2img`
ADD CONSTRAINT `tags2img_ibfk_1` FOREIGN KEY (`iID`) REFERENCES `images` (`iID`) ON DELETE CASCADE ON UPDATE CASCADE;
Now I use the following code to INSERT data into the tables
mysql_query("START TRANSACTION");
// opslaan van de serie description
$qry01 = "INSERT INTO series (description) VALUES ('$serie')";
$res01 = mysql_query($qry01, $conn);
$sID = mysql_insert_id($conn);
echo 'qry1<br>';
echo mysql_error().'<br>';
// opslaan van de tags
$tags2val = arrayPreApp($tags, "('", "')", ",");
$qry02 = "INSERT IGNORE INTO tags (description) VALUES $tags2val";
$res02 = mysql_query($qry02, $conn);
echo 'qry2<br>';
echo mysql_error().'<br>';
// opslaan van de image
$qry03 = "INSERT INTO images (sID, description, filename, price, date) VALUES ('$sID', '$description', '$filename', '$price', NOW())";
$res03 = mysql_query($qry03, $conn);
$iID = mysql_insert_id($conn);
echo 'qry3<br>';
echo mysql_error().'<br>';
// opslaan van de tags2img crosslinks
$tags2tID = arrayPreApp($tags, "(description = '", "')", " OR ");
$qry04 = "SELECT tID FROM tags WHERE $tags2tID";
$res04 = mysql_query($qry04, $conn);
echo 'qry3<br>';
echo mysql_error().'<br>';
while($row = mysql_fetch_array($res04)){
$tID[] = $row['tID'];
}
$tID2iID = arrayPreApp($tID, "(", ", $iID)", ", ");
$qry05 = "INSERT INTO tags2img (tID, iID) VALUES $tID2iID";
$res05 = mysql_query($qry05, $conn);
// committen van transactie
if($res01 && $res02 && $res03 && $res04 && $res05) {
mysql_query("COMMIT");
echo 'committed';
// TODO: retrun ok here
} else {
mysql_query("ROLLBACK");
echo 'rolled back';
// TODO: retrun error here
}
Works like a charm. Now when I want to DELETE data it only works partially;
$qry01 = "DELETE FROM series WHERE sID = 1";
// will delete the serie from series, the images with sID 1 from images and the crosslinks from tags2img with the iID
$qry02 = "DELETE FROM images WHERE iID = 1";
// will delete the image with sID 1 from images and the crosslinks from tags2img with the iID
This is all good. But I would also like to DELETE tags from the tags table that aren't being referenced anymore after crosslinks are DELETEd from tags2img.
I was under the impression I could set up some type of RELATION with a CASCADE RESTRICT function that would check if there are any RELATIONS between the tags table and the tags2img table and if not DELETE the tags.
I just can't figure out how... have I set it up all wrong or is this just not possible?
Cheers
