Strange creation table error (seems constraints or indexes are incorrect)

[KJedi]

I want to create the table:

         CREATE  TABLE  IF  NOT  EXISTS  `CompleteLookProducts` ( `primaryID` BIGINT( 20  )  NOT  NULL ,
 `secondaryID` BIGINT( 20  )  NOT  NULL ,
 `userID` INT( 11  )  NOT  NULL ,
 PRIMARY  KEY (  `primaryID` ,  `secondaryID` ,  `userID`  ) ,
 KEY  `fk_AlternativeProducts_ProductPrimary` (  `primaryID`  ASC  ) ,
 KEY  `fk_AlternativeProducts_User` (  `userID`  ASC  ) ,
 KEY  `fk_AlternativeProducts_ProductSecondary` (  `secondaryID`  ASC  ) ,
 CONSTRAINT  `fk_AlternativeProducts_ProductPrimary`  FOREIGN  KEY (  `primaryID`  )  REFERENCES  `Product` (  `productID`  )  ON  DELETE NO ACTION  ON  UPDATE NO ACTION ,
 CONSTRAINT  `fk_AlternativeProducts_User`  FOREIGN  KEY (  `userID`  )  REFERENCES  `User` (  `userID`  )  ON  DELETE NO ACTION  ON  UPDATE NO ACTION ,
 CONSTRAINT  `fk_AlternativeProducts_ProductSecondary`  FOREIGN  KEY (  `secondaryID`  )  REFERENCES  `Product` (  `productID`  )  ON  DELETE NO ACTION  ON  UPDATE NO ACTION 
) ENGINE  = InnoDB

It fails with error 121

While this query was successfully excecuted (before the previous one):

CREATE TABLE `AlternativeProducts` (
  `primaryID` BIGINT(20) NOT NULL,
  `secondaryID` BIGINT(20) NOT NULL,
  `userID` INT(11) NOT NULL,
  `type` enum('hotter','cheaper','hq','alt') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`primaryID`,`secondaryID`,`userID`,`type`),
  KEY `fk_AlternativeProducts_Product` (`primaryID`),
  KEY `fk_AlternativeProducts_User` (`userID`),
  KEY `fk_AlternativeProducts_Product1` (`secondaryID`),
 CONSTRAINT `fk_AlternativeProducts_Product` FOREIGN KEY (`primaryID`) REFERENCES `Product` (`productID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_AlternativeProducts_User` FOREIGN KEY (`userID`) REFERENCES `User` (`userID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_AlternativeProducts_Product1` FOREIGN KEY (`secondaryID`) REFERENCES `Product` (`productID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Just for reference, table structures of the Product and User table:

CREATE  TABLE IF NOT EXISTS `db`.`Product` (
  `productID` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `shopID` INT(11) NOT NULL ,
  `categoryID` INT(5) NOT NULL ,
  `url` TEXT NULL ,
  `name` VARCHAR(255) NOT NULL ,
  `description` TEXT NULL ,
  `image1` VARCHAR(150) NULL ,
  `image2` VARCHAR(150) NULL ,
  `image3` VARCHAR(150) NULL ,
  `image4` VARCHAR(150) NULL ,
  `popularity` FLOAT(2,1) NOT NULL ,
  `price` FLOAT(8,2) NOT NULL ,
  `previousPrice` FLOAT(8,2) NULL ,
  `dtAdded` DATETIME NOT NULL ,
  `dtReduced` DATETIME NULL ,
  `viewsNum` INT(11) NOT NULL DEFAULT 0 ,
  `colourID` INT(11) NOT NULL ,
  `brandID` INT NULL ,
  PRIMARY KEY (`productID`) ,
  INDEX `fk_Product_Category` (`categoryID` ASC) ,
  INDEX `fk_Product_Colour` (`colourID` ASC) ,
  INDEX `fk_Product_Brand` (`brandID` ASC) ,
  INDEX `fk_Product_Shop` (`shopID` ASC) ,
  CONSTRAINT `fk_Product_Category`
    FOREIGN KEY (`categoryID` )
    REFERENCES `db`.`Category` (`categoryID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Product_Colour`
    FOREIGN KEY (`colourID` )
    REFERENCES `db`.`Colour` (`colourID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Product_Brand`
    FOREIGN KEY (`brandID` )
    REFERENCES `db`.`Brand` (`brandID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Product_Shop`
    FOREIGN KEY (`shopID` )
    REFERENCES `db`.`Shop` (`shopID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
 
CREATE  TABLE IF NOT EXISTS `db`.`User` (
  `userID` INT(11) NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(50) NOT NULL ,
  `password` VARCHAR(50) NOT NULL ,
  `realAge` INT(3) NULL ,
  `fashionAge` INT(3) NULL ,
  `gender` ENUM('male','female') NOT NULL ,
  `buyPetite` TINYINT(1) NOT NULL ,
  `buyPlus` TINYINT(1) NOT NULL ,
  `shopMostPlace` VARCHAR(100) NULL ,
  `qualityOrPrice` ENUM('quality','price') NOT NULL ,
  `latestOrLast` ENUM('latest','last') NOT NULL ,
  `styleOrComfort` ENUM('style','comfort') NOT NULL ,
  `dtLastVisit` DATETIME NOT NULL ,
  `referrerURL` VARCHAR(255) NULL ,
  PRIMARY KEY (`userID`) )
ENGINE = InnoDB;

DB scheme (picture) is attached.

Any ideas why is it so?

[r937]

change your constraint names to be unique

you're using `fk_AlternativeProducts_User` as a constraint name in AlternativeProducts

you can't use the same constraint name in CompleteLookProducts