Search the Community

I'm trying to do pretty much the same thing as shown in this example: (http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html#Listing_4a). But when I try to insert rows in which the fk field value is NULL, I get the error message about "can not add or update a child row". This is for what amounts to an electronic checkbook. The transactions will have three possible settings that will be displayed as the category in the UI: a transfer to another account, a value from the defaultCategories table or a user defined category (categories table). So acctXID, defCatID or catID will have a value. There is another field ("type") that is an enum that will tell which of these should be expected. This is the table in question: CREATE TABLE IF NOT EXISTS `transactions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `ref` varchar(10) DEFAULT NULL, `date` timestamp NULL DEFAULT NULL, `payee` varchar(50) DEFAULT NULL, `amount` decimal(10,2) DEFAULT NULL, `memo` varchar(50) DEFAULT NULL, `catID` bigint(20) unsigned DEFAULT NULL, `acctXID` smallint(6) unsigned DEFAULT NULL, `acctID` smallint(6) unsigned DEFAULT NULL, `version` int(11) DEFAULT '0', `userID` bigint(20) DEFAULT NULL, `type` enum('normalD','normalU,'transfer') DEFAULT NULL, `parentID` bigint(20) unsigned DEFAULT NULL, `defCatID` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fkTxnParentID` (`parentID`), KEY `fkAcctID` (`acctID`), KEY `fkCatID` (`catID`), KEY `fkAcctXID` (`acctXID`), KEY `fkDefCatID` (`defCatID`), KEY `date` (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4875 ; ALTER TABLE `transactions` ADD CONSTRAINT `fkAcctID` FOREIGN KEY (`acctID`) REFERENCES `accounts` (`id`), ADD CONSTRAINT `fkAcctXID` FOREIGN KEY (`acctXID`) REFERENCES `accounts` (`id`), ADD CONSTRAINT `fkCatID` FOREIGN KEY (`catID`) REFERENCES `categories` (`id`), ADD CONSTRAINT `fkDefCatID` FOREIGN KEY (`defCatID`) REFERENCES `defaultCategories` (`id`), ADD CONSTRAINT `fkTxnParentID` FOREIGN KEY (`parentID`) REFERENCES `transactions` (`id`); This is the definition of the categories, defaultCategories and accounts tables. #categories Table CREATE TABLE IF NOT EXISTS `categories` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(48) NOT NULL, `userID` smallint(5) unsigned NOT NULL, `type` enum('income','expense') NOT NULL, `parentID` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `fkCatParentID` (`parentID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=215 ; #defaultCategories Table CREATE TABLE IF NOT EXISTS `defaultCategories` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(48) NOT NULL, `parentID` varchar(48) DEFAULT NULL, `type` enum('income','expense') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ; #accounts Table Definition CREATE TABLE IF NOT EXISTS `accounts` ( `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(48) NOT NULL, `type` enum('cash','credit','checking') NOT NULL DEFAULT 'checking', `userID` smallint(6) unsigned NOT NULL, `nextCheck` smallint(5) unsigned DEFAULT NULL, `beginBal` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Hey guys, I am looking into using MySQL's Spatial Extensions. However, I noticed that spatial indexes are not available for InnoDB tables which I am using to implement foreign key constraints. Instead, it is available on MyISAM tables, which do not support foreign keys. I have a table with user profiles and have a foreign key to a "city" table on which I would like to have a spatial index. To have this index, I need to use MyISAM, but then I have to throw my foreign key out the window. I can get the same performance if I add an index on the "foreign" key on the profile table; I can then simply join with the primary key of the "city" table just as before. The only difference is that I do not have a foreign key. This means that I lose the relationship constraint and managing the integrity of my key is then up to me. If I am not careful, I could add a wrong value which would not match anything in the "city" table and therefore a join cannot be done. Updating the "city" table will also be more complicated because then I have to update all rows in my referencing table myself. However, this is not a big concern. So, to sum up, to use the spatial index, I need to use MyISAM and lose my foreign key. Is the optimal solution to index my join columns properly and handle the correctness of my "foreign key" myself? Does anyone have any other ideas (or thumbs up to my approach)? Thanks.

My shared web host does not allow for InnoDB tables. I was planning to use them to take advantage of the foreign key constraint. Now I am going to try to make do with MyISAM, and it seems like the next best thing is a composite key. Would you agree with that? Where can I find information about how to correctly set that up? In my research, I keep finding endless information about foreign keys, and less about the concept of composite keys. Any help you can offer would be greatly appreciated. Thank you in advance for your consideration.