Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    20

    Unanswered: Cannot delete or update a parent row: a foreign key constraint fails

    Hi,

    I get an error message "Cannot delete or update a parent row: a foreign key constraint fails" when I try to do the following:
    Code:
    update crmentity, notes, senotesrel, tmpMax 
    set crmentity.crmid = tmpMax.crmid, 
        notes.notesid = tmpMax.crmid, 
        senotesrel.notesid = tmpMax.crmid 
    where crmentity.crmid=-1 and 
          notes.notesid=-1 and 
          senotesrel.notesid=-1 and 
          tmpMax.id = 1;
    The tables are:

    crmentity
    Code:
    CREATE TABLE `crmentity` (                                         
                 `crmid` int(19) NOT NULL default '0',                            
                 `smcreatorid` int(19) NOT NULL default '0',                      
                 `smownerid` int(19) NOT NULL default '0',                        
                 `modifiedby` int(19) NOT NULL default '0',                       
                 `setype` varchar(30) NOT NULL default '',                        
                 `description` longtext,                                          
                 `createdtime` datetime NOT NULL default '0000-00-00 00:00:00',   
                 `modifiedtime` datetime NOT NULL default '0000-00-00 00:00:00',  
                 `viewedtime` datetime default NULL,                              
                 `status` varchar(50) default NULL,                               
                 `version` int(19) NOT NULL default '0',                          
                 `presence` int(1) default '1',                                   
                 `deleted` int(1) NOT NULL default '0',                           
                 PRIMARY KEY  (`crmid`),                                          
                 KEY `crmentity_IDX0` (`smcreatorid`),                            
                 KEY `crmentity_IDX1` (`smownerid`),                              
                 KEY `crmentity_IDX2` (`modifiedby`)                              
    ) TYPE=InnoDB
    notes
    Code:
    CREATE TABLE `notes` (                                                                              
              `notesid` int(19) NOT NULL default '0',                                                           
              `contact_id` int(19) NOT NULL default '0',                                                        
              `title` varchar(50) NOT NULL default '',                                                          
              `filename` varchar(50) default NULL,                                                              
              `notecontent` longtext,                                                                           
              PRIMARY KEY  (`notesid`,`contact_id`),                                                            
              KEY `Notes_UK0` (`title`),                                                                        
              KEY `Notes_IDX0` (`notesid`),                                                                     
              CONSTRAINT `fk_Notes` FOREIGN KEY (`notesid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE  
    ) TYPE=InnoDB
    senotesrel
    Code:
    CREATE TABLE `senotesrel` (                                                                             
                  `crmid` int(19) NOT NULL default '0',                                                                 
                  `notesid` int(19) NOT NULL default '0',                                                               
                  PRIMARY KEY  (`crmid`,`notesid`),                                                                     
                  KEY `SeNotesRel_IDX0` (`notesid`),                                                                    
                  KEY `SeNotesRel_IDX1` (`crmid`),                                                                      
                  CONSTRAINT `fk_SeNotesRel` FOREIGN KEY (`notesid`) REFERENCES `notes` (`notesid`) ON DELETE CASCADE,  
                  CONSTRAINT `fk_SeNotesRel2` FOREIGN KEY (`crmid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE  
    ) TYPE=InnoDB
    Any clue why I'm getting this error? I know it's about the foreign keys and the reference between tables, but how I can do the update..?

    Thanks in advance for any help!

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by doze
    I get an error message "Cannot delete or update a parent row: a foreign key constraint fails" when I try to do the following:
    Code:
    update crmentity, notes, senotesrel, tmpMax 
    set crmentity.crmid = tmpMax.crmid, 
        notes.notesid = tmpMax.crmid, 
        senotesrel.notesid = tmpMax.crmid 
    where crmentity.crmid=-1 and 
          notes.notesid=-1 and 
          senotesrel.notesid=-1 and 
          tmpMax.id = 1;
    From http://dev.mysql.com/doc/mysql/en/update.html:

    If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly.


    --
    felix

Posting Permissions

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