Re: ERROR 1005: Can't create table (errno: 150) [Oracle Conversion Potential Solution]

[Kevin Clark]

I found this problem when converting from Oracle. Oracle can automatically convert FK references between datatypes. MySQL cannot.

So,

One table had a FK reference to another table and the types were not the same.

DROP TABLE IF EXISTS `test`.`attendee_event`;
CREATE TABLE `test`.`attendee_event` (
`event_id` DECIMAL(38, 0) NOT NULL,
`facilitator_sid` INT(5) NOT NULL,
`facilitator_eid` INT(5) NOT NULL,
...
CONSTRAINT `attendee_event__facilitatr_eid` FOREIGN KEY `attendee_event__facilitatr_eid` (`facilitator_eid`)
REFERENCES `test`.`contact_entity` (`entity_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,

The referenced table uses DECIMAL (38,0) for the column type. Works in Oracle but not in MySQL.

CREATE TABLE `test`.`contact_entity` (
`entity_id` DECIMAL(38, 0) NOT NULL,
...

Hope that saves someone some time...