CODEWITHSUNDEEP
mysqlruntime-error
Ali
Ali

Reputation: 8100

MySQL Can't create table (errno: 150) - table has no foreign key

1- I know this question has been asked several times. 2- I read them all 3- It didn't fix my problem 4- I know it's related to foreign key relationship

I just want to create a table that has no Foreign key but still I get this error 

Can't create table 'pwp.decision_nodes' (errno: 150)

Here is the table create statement

CREATE TABLE IF NOT EXISTS `decision_nodes` (
  `id` BIGINT(45) NOT NULL AUTO_INCREMENT,
  `decision_node_id` BIGINT(50) NOT NULL,
  KEY pk_index(`id`),
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8;

I even tried 

SET FOREIGN_KEY_CHECKS = 0;

and run the create statement but to no avail.

MySQL version

'5.5.21-log'

I am not exactly sure, but I think there was a table with the same name in the database previously ( long time ago) which was perhaps renamed or dropped. Can that be a hint?

That historical table (renamed or dropped) had foreign key relationships with two other tables help_entity and ref_cancer_type

CREATE TABLE `help_entity` (
  `id` bigint(50) NOT NULL AUTO_INCREMENT,
  `type` int(50) NOT NULL,
  `comments` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=171 DEFAULT CHARSET=utf8


CREATE TABLE `ref_cancer_type` (
  `id` char(3) NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  `is_active` tinyint(4) NOT NULL,
  `display_order` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Does this prove any point?

Upvotes: 1

Views: 454

Answers (1)

iiro
iiro

Reputation: 3118

I think some of your other tables have foreign key references to this table you now want to create.

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

If you re-create a table that was dropped, it must have a definition that conforms to the foreign
key constraints referencing it. It must have the right column names and types, and it must have 
indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error 
number 1005 and refers to error 150 in the error message.

Upvotes: 2

Related Questions