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

doze · #1 (**permalink**) 09-21-05, 06:49

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!

felixg · #2 (**permalink**) 09-21-05, 08:03

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

Internet Services

Web Development

Digital Marketing

Consumer Tech