Peter Brawley Wrote:
-------------------------------------------------------
> Parentless nodes should be null, not 0.
>
> Be aware that vanilla MySQL query syntax does not
> support recursion, so unless you've imposed a
> limit on possible tree depth, querying the tree
> requires procedural logic, see
>
http://www.artfulsoftware.com/mysqlbook/sampler/my
> sqled1ch20.html
I was able to get the DB to accept my fk definitions. But now that I'm actually trying to insert the data, if an fk field has a null value, then I get error messages.
Based on this specific portion of the link you provided, it seems like this should be working.
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html#Listing_4a
The table has three fields that reference the id field of three other tables. All but one of these fields will always be NULL and in some cases the third one will be null too.
The definition of 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('normal','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`);
And here are the tables that are referenced:
#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 ;
Don