Foreign Key Issue

Databases
1

I’ve got a table that contains a FK for lookups.
These are INT(10) unsigned, nullable because they won’t always have a valid entry.
When I try and update a row I get a “Database query failed: Cannot add or update a child row: a foreign key constraint fails”.
The statement looks like this “UPDATE table SET id=‘1’, idOfFK=‘’, next field”.
The idOfFK has 2 single quotes or NULL, right.

The object that’s building the UPDATE statement is being passed a value of NULL. How can I get the word NULL in a string. It seems that I can get ‘’ or ‘NULL’ but not just NULL.

I’m also not sure why there’s a constraint issued with the nullable field either, the field will sometimes not be there. How could I get a NULL entry in the auto_increment PK field; that doesn’t make sense.

I hope I haven’t confused you as much as I am confused :slight_smile:

Any thoughts would be appreciated.

John

2

please do a SHOW CREATE TABLE for both tables so that we can actually see the foreign key

and the problem you’re having with your “object” that cannot handle a simple SQL keyword like NULL… well, that’s not a mysql problem, is it

:cool:

3

Sorry for the ‘venting’; I was getting frustrated :slight_smile:


categories
CREATE TABLE `categories` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `nameCategory` varchar(100) NOT NULL,
 `idChg` int(10) unsigned NOT NULL,
 `dateChg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `idAdd` int(10) unsigned NOT NULL,
 `dateAdd` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 UNIQUE KEY `idx_category` (`nameCategory`),
 KEY `FK_categoriesChg` (`idChg`),
 KEY `FK_categoriesAdd` (`idAdd`),
 CONSTRAINT `FK_categoriesAdd` FOREIGN KEY (`idAdd`) REFERENCES `users` (`id`),
 CONSTRAINT `FK_categoriesChg` FOREIGN KEY (`idChg`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='Categories Table'
=========================================================
CREATE TABLE `titles` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `idCategories` int(10) unsigned DEFAULT NULL,
 `idEditions` int(10) unsigned DEFAULT NULL,
 `idFormats` int(10) unsigned DEFAULT NULL,
 `idLocations` int(10) unsigned DEFAULT NULL,
 `idPublishers` int(10) unsigned DEFAULT NULL,
 `idSeries` int(10) unsigned DEFAULT NULL,
 `nameTitle` varchar(100) NOT NULL,
 `subTitle` varchar(100) DEFAULT NULL,
 `cost` decimal(9,2) DEFAULT '0.00',
 `dateAcquired` date DEFAULT NULL,
 `dateRead` date DEFAULT NULL,
 `cntTimesRead` decimal(3,0) DEFAULT '0',
 `rating` char(1) DEFAULT NULL,
 `autographed` tinyint(1) NOT NULL DEFAULT '0',
 `isbn` char(20) DEFAULT NULL,
 `volumes` decimal(3,0) DEFAULT '1',
 `duration` decimal(11,0) DEFAULT '0',
 `idChg` int(10) unsigned NOT NULL,
 `dateChg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `idAdd` int(10) unsigned NOT NULL,
 `dateAdd` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 KEY `FK_titlesChg` (`idChg`),
 KEY `FK_titlesAdd` (`idAdd`),
 KEY `FK_titlesCategories` (`idCategories`),
 KEY `FK_titlesEditions` (`idEditions`),
 KEY `FK_titlesLocations` (`idLocations`),
 KEY `FK_titlesSeries` (`idSeries`),
 KEY `FK_titlesPublishers` (`idPublishers`),
 KEY `FK_titlesFormats` (`idFormats`),
 KEY `SK_isbn` (`isbn`),
 KEY `SK_title` (`nameTitle`),
 CONSTRAINT `FK_titles` FOREIGN KEY (`idPublishers`) REFERENCES `publishers` (`id`),
 CONSTRAINT `FK_titlesAdd` FOREIGN KEY (`idAdd`) REFERENCES `users` (`id`),
 CONSTRAINT `FK_titlesChg` FOREIGN KEY (`idChg`) REFERENCES `users` (`id`),
 CONSTRAINT `FK_titlesEditions` FOREIGN KEY (`idEditions`) REFERENCES `editions` (`id`),
 CONSTRAINT `FK_titlesFotmats` FOREIGN KEY (`idFormats`) REFERENCES `formats` (`id`),
 CONSTRAINT `FK_titlesLocations` FOREIGN KEY (`idLocations`) REFERENCES `locations` (`id`),
 CONSTRAINT `FK_titlesSeries` FOREIGN KEY (`idSeries`) REFERENCES `seriess` (`id`),
 CONSTRAINT `FK_titles_categories` FOREIGN KEY (`idCategories`) REFERENCES `categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=793 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='Titles Table'

Yes, the NULL stuff is how I’m formatting the INSERT/UPDATE statement.
I need to not include any columns for which there is no value.
That’s what I think is causing the problem.

Thanks for any suggestions.

John

4

your tables look fine, the FKs look fine

if you’re getting the “Cannot add or update a child row: a foreign key constraint fails” error message, it means you’re trying to add or change a FK value that is not present as a PK in the referenced table

of course, i cannot tell which FK is causing this, nor what its new value is that’s causing it

5

Yes, during the process of trying to explain this I realized that the issue isn’t with the tables so much as it’s with how I’m trying to update the tables so I’ve got to work on building the SQL statements correctly.

Thanks for listening.

John