MySQL Bugs: #3623: Cannot delete or update a parent row

Bug #3623	Cannot delete or update a parent row
Submitted:	1 May 2004 22:32	Modified:	11 Aug 2005 14:25
Reporter:	Heraldo Hernández	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When I set a Foreign Key ON DELETE NO ACTION and I try to delete de parent row, MySQL send this error message: #1217 - Cannot delete or update a parent row: a foreign key constraint fails 

    

How to repeat:
CREATE TABLE `tab_1` (
  `id` int(11) NOT NULL auto_increment,
  `dato` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB AUTO_INCREMENT=3 ;

INSERT INTO `tab_1` VALUES (1, 'hola 1');
INSERT INTO `tab_1` VALUES (2, 'hola 2');

# --------------------------------------------------------

CREATE TABLE `tab_2` (
  `id` int(11) NOT NULL default '0',
  `datos` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `id_ind` (`id`)
) TYPE=InnoDB;

INSERT INTO `tab_2` VALUES (1, 'dat1');
INSERT INTO `tab_2` VALUES (2, 'dat 2');

ALTER TABLE `tab_2`

  ADD CONSTRAINT `tab_2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `tab_1` (`id`) ON DELETE NO ACTION;

Suggested fix:
I don't know

you set "no action" and that's what you've got - no updates to the database

according to a user post
http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
by standards no action==restrict
previosu versions acted by disabling the key...

this mean that I can't delete a row on parent table without alter the children table??.

In other way, I must set On delete cascade, or on delete set null but NOT on delete no action because it's like on delete restrict??

A real NO ACTION option is yet to be done. 

It will be done in 4.1. branch.

NO ACTION actually means the same as RESTRICT :). This was fixed about 2 years ago.