Hi there, I have a table in which a column is referencing another column in the same table.
CREATE TABLE `Shops` ( `ShopId` smallint(8) unsigned zerofill NOT NULL auto_increment, `Shop` varchar(100) NOT NULL default '', `Node` smallint(8) unsigned default NULL, `Seq` smallint(5) unsigned NOT NULL default '0', `Lft` smallint(5) unsigned NOT NULL default '0', `Rgt` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`ShopId`), KEY `Node` (`Node`), CONSTRAINT `0_2042` FOREIGN KEY (`Node`) REFERENCES `Shops` (`ShopId`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB mysql> SELECT ShopId, Node FROM Shops; +----------+------+ | ShopId | Node | +----------+------+ | 00000001 | NULL | | 00000002 | 1 | | 00000003 | 1 | | 00000004 | 1 | | 00000005 | 1 | | 00000006 | 1 | +----------+------+ 6 rows in set (0.00 sec) As you can see, row #2 - row #6 has a column (Node) that references ShopId of row #1. When I issue: delete from Shops WHERE ShopId=1\g Row #2 - row#6 are deleted as well since there is an ON DELETE CASCADE constraint. However, UPDATE Shops SET ShopId=100 WHERE ShopId=1 gets me an error message: ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails Any ideas why this is occurring? Regards, Jindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
