CODEWITHSUNDEEP
mysqlforeign-keys
天佑 郑
天佑 郑

Reputation: 1

MySQL can't create table with foreign key

CREATE  TABLE `assessmentbookdb`.`MCQs` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `MCQAnswer` VARCHAR(200) NOT NULL ,
  `QuestionID` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `QuestionID` (`QuestionID` ASC) ,
  CONSTRAINT `QuestionID`
    FOREIGN KEY (`QuestionID` )
    REFERENCES `assessmentbookdb`.`Question` (`QuestionID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Message Log:

ERROR 1005: Can't create table 'assessmentbookdb.mcqs' (errno: 121) SQL Statement:

CREATE  TABLE `assessmentbookdb`.`MCQs` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `MCQAnswer` VARCHAR(200) NOT NULL ,
  `QuestionID` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `QuestionID` (`QuestionID` ASC) ,
  CONSTRAINT `QuestionID`
    FOREIGN KEY (`QuestionID` )
    REFERENCES `assessmentbookdb`.`Question` (`QuestionID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
    ENGINE = InnoDB

Upvotes: 0

Views: 2908

Answers (5)

Shee
Shee

Reputation: 1925

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

InnoDB supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. The syntax for an InnoDB foreign key constraint definition in the CREATE TABLE or ALTER TABLE statement looks like this: 

 [CONSTRAINT [symbol]] FOREIGN KEY
 [index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

 reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

Upvotes: -1

Jérôme Boé
Jérôme Boé

Reputation: 2948

Take a look Here and try to not give a name to your constraint.

Upvotes: 0

Markus Mikkolainen
Markus Mikkolainen

Reputation: 3497

based on googling i would assume that you have a constraint that exists with the same name that you try to add a constraint with. It might be that you didnt delete an old constraint from the old version of the table or something.

Upvotes: 2

Tom van der Woerdt
Tom van der Woerdt

Reputation: 29985

Double-check that:

  • The key's name is unique
  • The two keys you're coupling have the exact same datatype (here: INT NOT NULL), even signedness
  • The referencing fields actually exist

Upvotes: 6

juergen d
juergen d

Reputation: 204924

table creation failed because a foreign key constraint was not correctly formed

Somehow your foreign key is not correct. This can be if the table you are refering does not exist yet.

Upvotes: 0

Related Questions