Jump to content

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


doni49

Recommended Posts

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 by doni49
Link to comment
Share on other sites

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);
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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);
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.