triexa
February 26, 2010, 1:14pm
1
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!
r937
February 26, 2010, 3:35pm
2
can’t tell without seeing the CREATE TABLE statement for the articles table
triexa
February 26, 2010, 9:24pm
3
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
r937
February 26, 2010, 9:41pm
4
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
IBazz
February 27, 2010, 12:37am
5
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