Foreign Keys: Childless rows (NULL values in fk field)
I'm trying to do pretty much the same thing as shown in this example: (http://www.artfulsof...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 ;
Just to confirm that a foreign key field CAN have a null value, I temporarily removed the three foreign keys from the transactions able and inserted the following. It accepted it without complaint. Each transaction has a field called "parentID". There is a foriegn key (fkTxnParentID) in which this field references the id field for the same table.
KEY `fkTxnParentID` (`parentID`) < -- remains for this test. As you can see below, it worked fine.
KEY `fkAcctID` (`acctID`) <-- eliminated for this test.
KEY `fkCatID` (`catID`) <-- eliminated for this test.
KEY `fkAcctXID` (`acctXID`) <-- eliminated for this test.
KEY `fkDefCatID` (`defCatID`) <-- eliminated for this test.
A "top level" transaction has a null parentID. A child has a value in the parentID field that points to the top level transaction.
INSERT INTO `transactions` (`id`, `ref`, `date`, `payee`, `amount`, `memo`, `catID`, `acctXID`, `acctID`, `version`, `userID`, `type`, `parentID`, `defCatID`) VALUES
(4893, NULL, '2000-01-04 07:19:00', 'John Trivett', NULL, 'NULL', NULL, NULL, 1, 0, 4, NULL, NULL, NULL),
(4894, NULL, NULL, NULL, '8.76', NULL, 0, 2, NULL, 0, 4, NULL, 4893, 0),
(4895, NULL, NULL, NULL, '4.84', NULL, 207, 0, NULL, 0, 4, NULL, 4893, 0),
(4896, NULL, NULL, NULL, '2.28', NULL, 211, 0, NULL, 0, 4, NULL, 4893, 0);
Don