Foreign Keys: Childless rows (NULL values in fk field)

[doni49]

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 ;

Edited January 9, 2016 by doni49

[doni49]

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.  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);

[Muddy_Funster]

The child can only contain a null value if the parent contains a null value.  The point of a foreign key is that it forces relational values between the parent and child, all the parent ID fields that you are attaching to with the foreign keys are set to NOT NULL - as ID fields should be.  You can't have a NOT NULL parent and then insert NULL values in the child.

 

I think you should revisit your design.  At a glance: condensing "acctXID, defCatID or catID" into a single column e.g. refID and then having a second column to flag the reference source e.g. sourceID which you can match up that using a crossover table could be a better solution.

[ahmedkhan]

You can select null from your parent and then insert it your foreign key.

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, (SELECT NULL FROM defaultCategories) , 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);

[Muddy_Funster]

 

You can select null from your parent and then insert it your foreign key.

 

No they can't, because the table definitions for the parent side of the keys doesn't allow null values in the ID columns, as I mentioned already.  This means that the foreign key constraint will refuse the use of a null value in the child because it would break the referential integrity of the FK.