Reputation: 8100
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
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