Bug #23583 ERROR 1005 ON ALTER TABLE

• Submitted: 24 Oct 2006 15:31
• Modified: 25 Oct 2006 8:21
• Reporter: Filippo Monti
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.1.11-beta
• OS: Windows (Windows)
• CPU Architecture: Any
• Tags: 1005, error

[24 Oct 2006 15:31] Filippo Monti

Description:
When I run this script:

CREATE TABLE sg_gruppi (
  id 	    	  	INTEGER(9)	UNSIGNED NOT NULL AUTO_INCREMENT,
  codice      	  	VARCHAR(12)	NOT NULL,
  descrizione 	  	VARCHAR(35)	NOT NULL,
  des_cassa		VARCHAR(18),
  gruppo_padre_id	INTEGER(9),
  tipo_gruppo	  	INTEGER(1)	NOT NULL,
  iva_id		INTEGER(9),
  bilancia_id		INTEGER(9),
  bilancia_par		VARCHAR(100),
  decodifica_in		VARCHAR(10),
  decodifica_out	VARCHAR(10),
  PRIMARY KEY(id),
  CHECK (id > 0),
  UNIQUE KEY unq_gruppi(codice),
  CHECK (tipo_gruppo IN (0,1,2,3))
)
 ENGINE = InnoDB;
 ALTER TABLE sg_gruppi ADD CONSTRAINT fk_gruppi_gruppi_id FOREIGN KEY(gruppo_padre_id) REFERENCES sg_gruppi(id) ON DELETE CASCADE;

I receive this error:
ERROR 1005 (HY000): Can't create table 'gestire.#sql-67c_1b' (errno: 150)
The table is created but not the constraint

How to repeat:
Run those commands:

CREATE TABLE sg_gruppi (
  id 	    	  	INTEGER(9)	UNSIGNED NOT NULL AUTO_INCREMENT,
  codice      	  	VARCHAR(12)	NOT NULL,
  descrizione 	  	VARCHAR(35)	NOT NULL,
  des_cassa		VARCHAR(18),
  gruppo_padre_id	INTEGER(9),
  tipo_gruppo	  	INTEGER(1)	NOT NULL,
  iva_id		INTEGER(9),
  bilancia_id		INTEGER(9),
  bilancia_par		VARCHAR(100),
  decodifica_in		VARCHAR(10),
  decodifica_out	VARCHAR(10),
  PRIMARY KEY(id),
  CHECK (id > 0),
  UNIQUE KEY unq_gruppi(codice),
  CHECK (tipo_gruppo IN (0,1,2,3))
)
 ENGINE = InnoDB;
 ALTER TABLE sg_gruppi ADD CONSTRAINT fk_gruppi_gruppi_id FOREIGN KEY(gruppo_padre_id) REFERENCES sg_gruppi(id) ON DELETE CASCADE;

[24 Oct 2006 15:44] MySQL Verification Team

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

[25 Oct 2006 8:21] Filippo Monti

The problem arises because "id" is declared unsigned but not "gruppo_padre_id"
I've resolved the problem in this manner:

/* Formatted on 2006/10/25 10:16 (QP5 v5.50) */
CREATE TABLE `sg_gruppi` (
  `id` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `codice` varchar(12) NOT NULL,
  `descrizione` varchar(35) NOT NULL,
  `des_cassa` varchar(18) DEFAULT NULL,
  `gruppo_padre_id` int(9) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gruppi_gruppo_padre` (`gruppo_padre_id`),
  CONSTRAINT `fk_gruppi_gruppo_padre` FOREIGN KEY (`gruppo_padre_id`) REFERENCES `sg_gruppi` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thank's

Filippo