SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Foreign Key Issue

    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 :-)

    Any thoughts would be appreciated.

    John

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for the 'venting'; I was getting frustrated :-)

    Code:
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •