SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    #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:
    Code MySQL:
    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:
    Code:
    #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!
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    can't tell without seeing the CREATE TABLE statement for the articles table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here it is:

    Code MySQL:
    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
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •