Trouble with foreign keys

[doni49]

I'm trying to setup foreign keys for my DB.  It's accepting SOME of them but not all.  I'm hoping someone here might be able to assist.  This is for what is basically an electronic checkbook.

 

This is the table to which I'm trying to add my fk's.  All of this works (including the portion that adds fk's below -- they're the ones that it did accept) in fact I exported the table definition and that's what I'm showing you.

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ref` varchar(10) DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `payee` varchar(50) NOT NULL,
  `amount` decimal(10,2) NOT 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) NOT NULL,
  `userID` bigint(20) NOT NULL,
  `type` enum('normal','transfer') NOT NULL DEFAULT 'normal',
  `parentID` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fkTxnParentID` (`parentID`),
  KEY `fkAcctID` (`acctID`),
  KEY `fkCatID` (`catID`),
  KEY `fkAcctXID` (`acctXID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=214 ;
ALTER TABLE `transactions`
  ADD CONSTRAINT `fkAcctID` FOREIGN KEY (`acctID`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fkTxnParentID` FOREIGN KEY (`parentID`) REFERENCES `transactions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Then I have two other tables (accounts and categories).  Each transaction has a field called "acctID" that references the id field in the accounts table and a field called "catID" that references the id field in the categories table. 

 

Each transaction also has an enum field called "type":

• If transaction.type = 'transfer' then acctXID refers to the id number of the account to which the money is being transferred.  So I'm trying to put a second fk that also points to the id field in the accounts table.
• If transaction.type = 'normal' then catID should reference the id field in the categories table.

 

This is what the accounts & categories tables look like.

#accounts table
CREATE TABLE `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 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


#categories table
CREATE TABLE `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=102 ;


ALTER TABLE `categories`
  ADD CONSTRAINT `fkCatParentID` FOREIGN KEY (`parentID`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Now this is my attempt to add the new fk's to the transactions table.  It just throws the following error message:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`#sql-588b_17858f1`, CONSTRAINT `fkCatID` FOREIGN KEY (`catID`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

[doni49]

Anybody have any advice on this one?

[gizmola]

The message is telling you that there is no matching categories.id value for the transactions row you are trying to add.  A row with whatever id you are trying to specify in your insert must exist in the categories table.  This is how foreign keys work.

[doni49]

Ok thanks that does make sense.  Because I'm getting these messages when trying to CREATE the fk's, I was focused on the possibility that I had an error in the code I was using to create them.