Bug #16128 Can't create a Father-Son casdaded primary key

• Submitted: 2 Jan 2006 15:26
• Modified: 2 Jan 2006 15:47
• Reporter: Andre Pires
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.15
• OS: Windows (Windows XP SP2)
• CPU Architecture: Any

[2 Jan 2006 15:26] Andre Pires

Description:
I'm trying to create a cascaded father to son primary key. And I'm receiving the Server Error 1005 when trying to aply the ALTER TABLE command.

Sometimes the same error appears when I already have a cascaded foreing key in a table and try to create a second index, cascaded or not.

How to repeat:
Create the following tables:

DROP TABLE IF EXISTS `test`.`father`;
CREATE TABLE  `test`.`father` (
  `IdFather` int(10) unsigned NOT NULL auto_increment,
  `Data` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`IdFather`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `test`.`son`;
CREATE TABLE  `test`.`son` (
  `IdSon` int(10) unsigned NOT NULL auto_increment,
  `Data` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`IdSon`),
  CONSTRAINT `FK_Son_Father` FOREIGN KEY (`IdSon`) REFERENCES `father` (`IdFather`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

After the creation, aply the following ALTER TABLE command:

ALTER TABLE `test`.`son` DROP FOREIGN KEY `FK_Son_Father`,
 ADD CONSTRAINT `FK_Son_Father` FOREIGN KEY `FK_Son_Father` (`IdSon`)
    REFERENCES `father` (`IdFather`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

I'm receiving the following error:

MySQL Error Number 1005
Can't create table '.\test\#sql-73c_3.frm' (errono: 121)

[2 Jan 2006 15:47] Andre Pires

Sorry... not a Bug but I discover that in order to the ALTER TABLE command to function correctly both fields in the parent and son table must be identical in type, size and extra info, and only the father can be auto_incremented.

That info is not clear in the documentation and the error message does not help into finding the error source... At least change the error code and message in order to help developers to find the correct origin of the error...

Thanks.