#1005: Cannot create table.frm (error 121)

I generally use phpMyAdmin but am using MySQL workbench to set up everything ahead of time and organize it.

I right click my table, and “Copy SQL to clipboard”. This is what I got:

CREATE  TABLE IF NOT EXISTS `mydb`.`tblArticleClicks` (
  `click_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `article_id` INT UNSIGNED NULL DEFAULT 0 ,
  `click_ip` VARCHAR(15) NULL ,
  `click_ip_long` INT UNSIGNED NULL DEFAULT 0 ,
  `click_time` INT UNSIGNED NULL DEFAULT 0 ,
  PRIMARY KEY (`click_id`) ,
  INDEX `FK_ARTICLE_ID` (`article_id` ASC) ,
  CONSTRAINT `FK_ARTICLE_ID`
    FOREIGN KEY (`article_id` )
    REFERENCES `mydb`.`tblArticles` (`article_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

This is what I get in return:

#1005 - Can't create table './mydb/tblArticleClicks.frm' (errno: 121)

Have I set it up wrong/is it invalid SQL/something I need to do first? Any insight greatly appreciated!

can’t tell without seeing the CREATE TABLE statement for the articles table

Here it is:

CREATE  TABLE IF NOT EXISTS `mydb`.`tblArticles` (
  `article_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `author_id` INT UNSIGNED NULL DEFAULT 0 ,
  `article_title` VARCHAR(100) NULL ,
  `article_url_title` VARCHAR(100) NULL ,
  `article_body` TEXT NULL ,
  `article_source` VARCHAR(1024) NULL ,
  `article_date_created` INT UNSIGNED NULL DEFAULT 0 ,
  `article_date_publish` INT UNSIGNED NULL DEFAULT 0 ,
  `article_date_lastedit` INT UNSIGNED NULL DEFAULT 0 ,
  `article_status` TINYINT UNSIGNED NULL DEFAULT 0 ,
  PRIMARY KEY (`article_id`) ,
  INDEX `IDX_STATUS` (`article_status` ASC) ,
  INDEX `IDX_DATE_PUB` (`article_date_publish` ASC) ,
  INDEX `IDX_AUTHOR` (`author_id` ASC) ,
  INDEX `FK_ARTICLE_AUTHOR` (`author_id` ASC) ,
  CONSTRAINT `FK_ARTICLE_AUTHOR`
    FOREIGN KEY (`author_id` )
    REFERENCES `mydb`.`tblAuthors` (`author_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

hmm… the specs for the two columns are identical (this error often comes up when they aren’t, e.g. when one is INT and the other is INT UNSIGNED)

try giving the constraint a different name – constraint names have to be unique across the database, and maybe there’s another table with a constraint called FK_ARTICLE_ID

I don’t know that my suggestion would be everyones choice but; to try to prevent FK constraint name conflicts, I call them by their two table names which in this case would be ~ articles_article_clicks_fk

Decided upon this when I ran into that sort of problem and it hasn’t let me down yet.

hth

bazz