SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict KJedi's Avatar
    Join Date
    Sep 2005
    Location
    Ukraine, Nikolaev
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I want to create the table:
    Code SQL:
             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):
    Code SQL:
    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:
    Code SQL:
    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?
    Attached Images Attached Images
    Last edited by KJedi; Nov 30, 2008 at 06:07. Reason: spelling

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


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
  •