MySQL Bugs: #5103: Cannot add or update a child row: a foreign key constraint fails

Bug #5103	Cannot add or update a child row: a foreign key constraint fails
Submitted:	19 Aug 2004 0:49	Modified:	9 May 2007 10:08
Reporter:	Zachary Nguyen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.0.20-standard	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I created a parent/child table below. 
Inserted id=1,pid=NULL,c='a' and 
Inserted id=2,pid=1,c='b'.

Then do the update and set id=5 where id=1 return me the following error message.
"Cannot add or update a child row: a foreign key constraint fails".

The "ON DELETE CASCADE" worked fine.

CREATE TABLE `categories` (
  `id` int(11) NOT NULL default '0',
  `pid` int(11) default NULL,
  `c` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB

How to repeat:
created a parent/child table below. 

Inserted id=1,pid=NULL,c='a' and 
Inserted id=2,pid=1,c='b'.

Then do the update and set id=5 where id=1 

CREATE TABLE `categories` (
  `id` int(11) NOT NULL default '0',
  `pid` int(11) default NULL,
  `c` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB

Verified also on 4.1. Also UPDATE categories SET pid=5 WHERE pid=1 fails.

Hi!

This is the documented behavior.

http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
"
A deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has already updated during the cascade, it acts like RESTRICT.
"

Thus, this indeed is a feature request to allow cascaded updates within the SAME table.

InnoDB currently does not allow then because they could lead to an infinite loop of updates.

Regards,

Heikki

If i have circular reference between to tables in what cases it could have troubles... my tables are:

CREATE TABLE `producto` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `tipo` int(10) unsigned NOT NULL default '0',
  `moneda` int(10) unsigned NOT NULL default '0',
  `unidad_principal` int(10) unsigned default '0',
  `unidad_pequena` int(10) unsigned default '0',
  `codigo_externo` varchar(20) character set latin1 default NULL,
  `codigo_interno` varchar(20) character set latin1 default NULL,
  `descripcion` varchar(100) character set latin1 NOT NULL default '',
  `foto` varchar(30) character set latin1 default NULL,
  `cantidad_minima` int(10) unsigned NOT NULL default '0',
  `unidad_cantidad_minima` int(10) unsigned default '0',
  `costo_unitario` decimal(11,5) NOT NULL default '0.00000',
  `precio_venta` decimal(11,5) NOT NULL default '0.00000',
  `ice` decimal(5,2) NOT NULL default '0.00',
  `saldo_fisico` int(11) default NULL,
  `saldo_val` decimal(11,5) default NULL,
  `saldo_fisico_cons` int(11) default NULL,
  `saldo_val_cons` decimal(11,5) default NULL,
  `fecha_cons` datetime default NULL,
  `imagen` varchar(20) character set latin1 default NULL,
  `ubicacion` varchar(20) character set latin1 default NULL,
  `descripcion_detallada` varchar(250) character set latin1 default NULL,
  `tipo_vencimiento` char(1) character set latin1 default NULL,
  `precio_unitario_unidad_principal` decimal(11,5) NOT NULL default '0.00000',
  `tipo_precio` char(1) collate latin1_spanish_ci NOT NULL default '',
  `ice_unidad` int(10) unsigned default '0',
  `serie` char(1) collate latin1_spanish_ci default NULL,
  `lote` char(1) collate latin1_spanish_ci default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `descripcion` (`descripcion`),
  UNIQUE KEY `Index_7` (`codigo_interno`),
  KEY `tipo` (`tipo`),
  KEY `moneda` (`moneda`),
  KEY `unidad_principal` (`unidad_principal`),
  KEY `unidad_pequena` (`unidad_pequena`),
  KEY `producto_ibfk_11` (`unidad_cantidad_minima`),
  KEY `producto_ibfk_12` (`ice_unidad`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci COMMENT='InnoDB free: 6144 kB; (`tipo`) REFER `negnet/producto_tipo`(';

CREATE TABLE `unidad` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `producto` int(10) unsigned default '0',
  `descripcion` varchar(25) character set latin1 NOT NULL default '',
  `equivalencia` int(10) unsigned default NULL,
  `factor` decimal(11,5) NOT NULL default '0.00000',
  `precio_venta` decimal(11,5) NOT NULL default '0.00000',
  `factor_precio` decimal(11,5) NOT NULL default '0.00000',
  PRIMARY KEY  (`id`),
  KEY `unidad_ibfk_2` (`equivalencia`),
  KEY `FK_unidad_2` (`producto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci COMMENT='InnoDB free: 6144 kB; (`equivalencia`) REFER `negnet/unidad`';

Isn't this a duplicate of #3439 ?

Yes,this is a duplicate of Bug #3439. I'm sorry for not flagging it earlier. (If only the MySQL bug database featured a useable full-text search.)