CODEWITHSUNDEEP
mysqlsqlmagento
numerical25
numerical25

Reputation: 10790

#1005 - Can't create table errno: 150 Magento

I am trying to create a new table for magento and I am trying to reference existing magento tables. From what I googled, the problem that I am getting can be 1 of the 2 issues.

  1. The FK must have a index
  2. The PK must exist before the FK can reference

In both cases, I believe I did both of these correctly. Below is existing table schemas

ALREADY EXISTING TABLES BEING REFERENCED

CREATE TABLE IF NOT EXISTS `core_store` (
  `store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(32) NOT NULL DEFAULT '',
  `website_id` smallint(5) unsigned DEFAULT '0',
  `group_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL,
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0',
  `is_active` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`store_id`),
  UNIQUE KEY `code` (`code`),
  KEY `FK_STORE_WEBSITE` (`website_id`),
  KEY `is_active` (`is_active`,`sort_order`),
  KEY `FK_STORE_GROUP` (`group_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Stores' AUTO_INCREMENT=9 ;


CREATE TABLE IF NOT EXISTS `admin_user` (
  `user_id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL DEFAULT '',
  `lastname` varchar(32) NOT NULL DEFAULT '',
  `email` varchar(128) NOT NULL DEFAULT '',
  `username` varchar(40) NOT NULL DEFAULT '',
  `password` varchar(100) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified` datetime DEFAULT NULL,
  `logdate` datetime DEFAULT NULL,
  `lognum` smallint(5) unsigned NOT NULL DEFAULT '0',
  `reload_acl_flag` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `extra` text,
  `failures_num` smallint(6) NOT NULL DEFAULT '0',
  `first_failure` datetime DEFAULT NULL,
  `lock_expires` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `UNQ_ADMIN_USER_USERNAME` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Users' AUTO_INCREMENT=25 ;

Table I am trying to create

CREATE TABLE `oro_dashboard` 
( `id` int unsigned NOT NULL, 
`name` varchar(255) NOT NULL default '', 
`description` varchar(64) NOT NULL default '', 
`created_by` int unsigned NOT NULL default '0', 
`created_at` date, 
`layout` varchar(255) NOT NULL default '', 
`default_store_id` int, 
PRIMARY KEY (`id`), 
KEY `IDX_ORO_DASHBOARD_CREATED_BY` (`created_by`), 
CONSTRAINT `FK_ORO_DASHBOARD_CREATED_BY_ADMIN_USER_USER_ID` FOREIGN KEY (`created_by`) REFERENCES `admin_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, 
CONSTRAINT `FK_ORO_DASHBOARD_DEFAULT_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`default_store_id`) REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE ) 
ENGINE=INNODB charset=utf8 COLLATE=utf8_unicode_ci

In magento, I found there is in deed a table called admin_user that does have a column called user_id. There is a core_store table that does have a column called store_id. and both my columns have INDEXES made.

Does anyone have any clue what the issue maybe ? Below is my error message 

#1005 - Can't create table 'db.oro_dashboard' (errno: 150)

Upvotes: 1

Views: 1443

Answers (3)

Oleg Ishenko
Oleg Ishenko

Reputation: 2233

It appears that the column definition for default_store_id does not match core_store.store_id. it should be smallint(5) unsigned in your table. created_by has the same problem, although it did not prevent the table from being created

Upvotes: 2

John Woo
John Woo

Reputation: 263693

When defining foreign keys, the data type must be the same.

You are defining core_store.store_id as smallint(5) unsigned and so the referencing column must be the same: oro_dashboard.default_store_id.

Do also with oro_dashboard.created_by

Final oro_dashboard CREATE TABLE query,

CREATE TABLE `oro_dashboard` 
( `id` int unsigned NOT NULL, 
`name` varchar(255) NOT NULL default '', 
`description` varchar(64) NOT NULL default '', 
`created_by` mediumint(9) unsigned NOT NULL default '0', 
`created_at` date, 
`layout` varchar(255) NOT NULL default '', 
`default_store_id` smallint(5) unsigned,
 PRIMARY KEY (`id`),
 KEY `IDX_ORO_DASHBOARD_CREATED_BY` (`created_by`) ,
 CONSTRAINT `FK_ORO_DASHBOARD_CREATED_BY_ADMIN_USER_USER_ID` 
     FOREIGN KEY (`created_by`) 
     REFERENCES `admin_user` (`user_id`) 
     ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `FK_ORO_DASHBOARD_DEFAULT_STORE_ID_CORE_STORE_STORE_ID` 
     FOREIGN KEY (`default_store_id`) 
     REFERENCES `core_store` (`store_id`) 
     ON DELETE SET NULL ON UPDATE CASCADE 
)

Upvotes: 3

Meherzad
Meherzad

Reputation: 8553

It is necessary to have data type to be same for the foreign key and its corresponding parent key. The data type is different of the foreign key and its referencing parent key in your table that is why it is giving an error.

created by is int unsigned while userid is medium int in parent table

same is problem in the second foreign key

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

Upvotes: 3

Related Questions