MYSQL error no 121. Supports transactions, row-level locking, and foreign keys

Question

I am getting the error no 121 when running the below script. is anyone have any clue as whats wrong with the script?

-- -----------------------------------------------------
-- Table `Commitment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Commitment` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `type` CHAR(1) NOT NULL DEFAULT '0' COMMENT '0:Eco Commitment|1:Community Commitment|etc',
  `title` VARCHAR(180) NULL DEFAULT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `createdById` INT(11) NOT NULL DEFAULT -1,
  `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updatedById` INT(11) NULL DEFAULT NULL,
  `updatedAt` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '0:Delete|1:Active|2:Deactive|3:Pending|4:Blocked|5:Suspend|etc',
  PRIMARY KEY (`id`),
  INDEX `idxCreatedById` (`createdById` ASC),
  INDEX `idxUpdatedById` (`updatedById` ASC),
  CONSTRAINT `fkProductUser1`
  FOREIGN KEY (`createdById`)
  REFERENCES `User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fkProductUser2`
  FOREIGN KEY (`updatedById`)
  REFERENCES `User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8
  COLLATE = utf8_unicode_ci;

You don't use `fkProductUser1` or `fkProductUser2` in other tables as foreign keys, do you? [(Source)](http://stackoverflow.com/questions/1180660/mysql-errorno-121) — Chris Forrence, Feb 18 '14 at 05:42
Your error is with `FOREIGN KEY` constraint. Check the datatype and column name of the referencing table — G one, Feb 18 '14 at 05:48
@ChrisForrence yes thats the issue, fkProductUser1 is used in some other table unfortunately. please post this as answer to accept. — dev1234, Feb 18 '14 at 05:59
@Gone its the issue as Chris mentioned, thanks for the quick response. — dev1234, Feb 18 '14 at 05:59

score 2 · Accepted Answer · answered Feb 18 '14 at 12:52

The error code indicates that those foreign key names are already in use. What I'd do is, to make a key unique and memorable, is to use

Key type (idx, fk, ...)
Table name
Local column name
Referenced column name (or referenced table)

In your case, your foreign keys might be

fk_commitment_createdbyid_user_id
fk_commitment_updatedbyid_user_id

MYSQL error no 121. Supports transactions, row-level locking, and foreign keys

1 Answers1