Problem nachträglicher Fremdschlüsselzuordnung zu MySQL-Tables (constraints)

Posted in Mysql, 09.08.2013 06:08

Beim Anlegen eines Fremdschlüssel-Constraints auf eine Tabellenspalte kann es, auch wenn das Feld einen Index hat und beide Tabellen Innodb sind,  zu einem undurchsichtigen "Can't create Table"-Fehler kommen.

MySQL Error Number 1005Can’t create table ‘.\mydb\#sql-328_45.frm’ (errno: 150)

Nach langen Recherchen hat dann dieser Blog die Lösung gebracht:

Die Spalte mit dem Fremdschlüssel muß nicht nur "int(11)" sein, sondern "unsigned" und "NOT NULL". 

Hier die wichtigsten Voraussetzungen, um Fremdschlüssel einer Tabelle zuordnen zu können, sind folgende:

  • beides müssen Innodb-Tabellen sein

  • referenzierendes und referenziertes Feld müssen vom gleichen Typ und gleicher Länge sein(also z.B. BEIDE int(11) und nicht einmal int(11) und bigint)

  • das Feld muß einen Index (KEY) mit eindeutigem Namen haben oder der Primary Key sein

  • das foreignkey-Feld darf keinen Default value haben!

  • beide Tabelle und die DB müssen dieselben Charsets verwenden!

Dieses Statement funktioniert:

 
DROP TABLE IF EXISTS `manufacturer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `manufacturer` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `product`;

CREATE TABLE IF NOT EXISTS `product` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
`manufacturer_id` int(11) unsigned NOT NULL, 
`name` varchar(255) NOT NULL, 
`additional` varchar(255) DEFAULT NULL, 
`price` float(5,2) NOT NULL,  
`availability` int(11) unsigned NOT NULL, 
`product_image` varchar(255) NOT NULL, 
PRIMARY KEY (`id`), 
KEY `manufacturer_id` (`manufacturer_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

ALTER TABLE `product` 
ADD CONSTRAINT FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturer` (`ID`) 
ON DELETE CASCADE;