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 ;