Re: ERROR 1005: Can't create table (errno: 150)

[C Fischer]

Hello all,

I found out problems with Integer as PK,FK.

If you change the columns DEC(10) to INT(11) you will get an 1005-150.

Have anybody an idea why? I is this a bug?

MySQL 5.124

-- phpMyAdmin SQL Dump
-- version 2.11.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 30. Mai 2008 um 14:13
-- Server Version: 5.1.24
-- PHP-Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- --------------------------------------------------------

show engine innodb status;
DROP database if exists skeleton;
show warnings;
CREATE DATABASE `skeleton`;
show warnings;
use skeleton;


DROP TABLE IF EXISTS `skeleton_dim1`;
CREATE TABLE IF NOT EXISTS `skeleton_dim1` (
`id` DEC(10) NOT NULL ,
`objID` int(11) NOT NULL DEFAULT '-1' ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

DROP TABLE IF EXISTS `skeleton_dim2`;
CREATE TABLE IF NOT EXISTS `skeleton_dim2` (
`id` DEC(10) NOT NULL,
`objID` int(11) NOT NULL DEFAULT '-1' ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


-- --------------------------------------------------------


DROP TABLE IF EXISTS skeleton_fakt;
CREATE TABLE IF NOT EXISTS skeleton_fakt (
`id` int(11) NOT NULL AUTO_INCREMENT,
`objID` int(11) NOT NULL DEFAULT '-1' COMMENT 'DWH-ID',
`fk_skeleton_dim1` DEC(10) NOT NULL DEFAULT '-2' COMMENT 'Zeigt auf Dimension',
`fk_skeleton_dim2` DEC(10) NOT NULL DEFAULT '-2' COMMENT 'Zeigt auf Dimension',
PRIMARY KEY (`id`),
KEY `fk_skeleton_fakt_skeleton_dim2` (`fk_skeleton_dim2`),
KEY `fk_skeleton_fakt_skeleton_dim2_obj` (`objID`),
KEY `fk_skeleton_fakt_skeleton_dim1` (`fk_skeleton_dim1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

alter table skeleton_fakt add constraint fk_dim1 foreign key (fk_skeleton_dim1) references skeleton_dim1(id);
show warnings;
-- show engine innodb status;
alter table skeleton_fakt add constraint fk_dim2 foreign key (fk_skeleton_dim2) references skeleton_dim2(id);
show warnings;
-- show engine innodb status;