CODEWITHSUNDEEP
mysql
Ronedog
Ronedog

Reputation: 2331

why Mysql is giving me error 1280 "Wrong Index"

Can anyone explain why Mysql is giving me error 1280 ("wrong index for 'fk_chart_aid_aid' ") error whend I try to create the "CHART OF ACCOUNTS" table. I'm completly confused here. How can I fix this so I can create the table? The "ACCOUNT" table already exists in the database and has data in it.

Thanks for the help.

MYSQL Server version: 5.1.54

CHART OF ACCOUNTS:

DROP TABLE IF EXISTS `rst`.`acctg_chart_of_accounts` ;
CREATE  TABLE IF NOT EXISTS `rst`.`acctg_chart_of_accounts` (
  `acctg_chart_of_accounts_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `account_id` INT UNSIGNED NOT NULL ,
  `account_nbr` VARCHAR(45) NULL ,
  `description` VARCHAR(45) NULL ,
  `account_type` INT UNSIGNED NULL ,
  `commissionable` TINYINT UNSIGNED NULL ,
  `hidden` TINYINT UNSIGNED NULL ,
  `deduct_balance_from_owner_check` TINYINT UNSIGNED NULL ,
  PRIMARY KEY (`acctg_chart_of_accounts_id`) ,
  CONSTRAINT `fk_chart_aid_aid`
    FOREIGN KEY (`account_id` )
    REFERENCES `rst`.`account` (`account_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)    
ENGINE = InnoDB;
CREATE INDEX `fk_chart_aid_aid` ON `rst`.`acctg_chart_of_accounts` (`account_id` ASC) ;

ACCOUNTS TABLE THAT IS BEING REFERENCED:

CREATE TABLE IF NOT EXISTS `account` (
  `account_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_status_id` int(10) unsigned NOT NULL,
  `company_name` varchar(155) DEFAULT NULL,
  `address1` varchar(155) DEFAULT NULL,
  `address2` varchar(155) DEFAULT NULL,
  `city` varchar(155) DEFAULT NULL,
  `state` varchar(155) DEFAULT NULL,
  `zip` varchar(45) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `work_phone` varchar(45) DEFAULT NULL,
  `mobile_phone` varchar(45) DEFAULT NULL,
  `time_zone` varchar(45) DEFAULT NULL,
  `subdomain` varchar(155) DEFAULT NULL,
  `cname_URL` varchar(255) DEFAULT NULL,
  `promotion_code` varchar(45) DEFAULT NULL,
  `can_we_contact_you` tinyint(4) DEFAULT NULL COMMENT '0=false, 1=true',
  `units_managed_nbr` varchar(10) DEFAULT NULL,
  `a_hear_about_us_list_id` tinyint(3) unsigned DEFAULT NULL COMMENT 'populated from dropdown list.',
  `receive_special_offers` tinyint(4) DEFAULT NULL,
  `receive_announcements` tinyint(4) DEFAULT NULL,
  `receive_newsletter` tinyint(4) DEFAULT NULL,
  `create_ts` timestamp NULL DEFAULT NULL,
  `expires` timestamp NULL DEFAULT NULL,
  `storage_capacity` varchar(255) DEFAULT NULL COMMENT '1073741824 = 1GB',
  `logo` varchar(455) DEFAULT NULL,
  `max_active_connections` int(11) DEFAULT '3',
  `_product_id` int(11) DEFAULT NULL,
  `report_footer` varchar(455) DEFAULT NULL,
  `welcome_dialog` tinyint(4) DEFAULT '1',
  `ARB_subscription_id` int(11) DEFAULT NULL,
  `trashbin` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`account_id`),
  KEY `fk_account_account_status_id` (`account_status_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=58 ;

Upvotes: 4

Views: 3677

Answers (2)

daniel
daniel

Reputation: 11

I met the same issue; tried manually rename the index to a different name but don't like the idea of 'manually' and neither I don't really understand why we need to generate the index separately. so I decide to generate it within the create statement by unchecking the option of 'generate separate index statement' in 'forwarding engineer', and this fix the issue.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270617

Are you getting the error after the CREATE TABLE statement, or after the subsequent CREATE INDEX?

Looks like you are attempting to name both a FOREIGN KEY constraint and an INDEX fk_chart_aid_aid. Try choosing a different name for either one of them.

Also, in the accounts table, account_id is INT(10). Try also to change the column definition in acctg_chart_of_accounts to:

`account_id` INT(10) UNSIGNED NOT NULL ,

Though, I think that mysql defaults type INT to INT(10) anyway...

Upvotes: 5

Related Questions