Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    7

    Unanswered: SQL script failing error code 121

    I have a create script like this (segment):


    -- -----------------------------------------------------
    -- Table `Objects`.`childrencomponents`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `objects`.`childrencomponents` (
    `ObjectID` BIGINT NOT NULL ,
    `ComponentID` BIGINT NOT NULL ,
    `PosX` FLOAT NULL ,
    `PosY` FLOAT NULL ,
    `PosZ` FLOAT NULL ,
    `RotX` FLOAT NULL ,
    `RotY` FLOAT NULL ,
    `RotZ` FLOAT NULL ,
    `ScaleX` FLOAT NULL ,
    `ScaleY` FLOAT NULL ,
    `ScaleZ` FLOAT NULL ,
    PRIMARY KEY (`ObjectID`, `ComponentID`, `PosX`, `PosY`, `PosZ`) ,
    INDEX `ObjectID` (`ObjectID` ASC) ,
    INDEX `ComponentID` (`ComponentID` ASC) ,
    CONSTRAINT `ObjectID`
    FOREIGN KEY (`ObjectID` )
    REFERENCES `Objects`.`objectlist` (`ObjectID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `ComponentID`
    FOREIGN KEY (`ComponentID` )
    REFERENCES `objects`.`componentlist` (`ComponentID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB
    COMMENT = 'List of components associated with each object';

    -- -----------------------------------------------------
    -- Table `Objects`.`childrenobjects`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `objects`.`childrenobjects` (
    `ObjectID` BIGINT NOT NULL ,
    `ChildObjectID` BIGINT NULL ,
    `PosX` FLOAT UNSIGNED NULL ,
    `PosY` FLOAT UNSIGNED NULL ,
    `PosZ` FLOAT UNSIGNED NULL ,
    `RotX` FLOAT NULL ,
    `RotY` FLOAT NULL ,
    `RotZ` FLOAT NULL ,
    `ScaleX` FLOAT NULL ,
    `ScaleY` FLOAT NULL ,
    `ScaleZ` FLOAT NULL ,
    PRIMARY KEY (`ObjectID`, `ChildObjectID`, `PosX`, `PosY`, `PosZ`) ,
    INDEX `ObjectID` (`ObjectID` ASC) ,
    CONSTRAINT `ObjectID`
    FOREIGN KEY (`ObjectID` )
    REFERENCES `objects`.`objectlist` (`ObjectID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB
    COMMENT = 'Objects that are children of other objects';


    The 2nd table fails. Why? Do I need to change the name of the ObjectID field?
    Last edited by dontforget86; 05-15-10 at 17:53.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "fails" is not descriptive enough for us to troubleshoot adequately

    try creating the 2nd table without the foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    7
    The table creation fails with error code 121. I'm using a program called SQL maestro to try and execute the script. I'll see if I can find more debug info.

    This is what my database looks like:
    http://dl.dropbox.com/u/1735585/embeddeddatabase.png

    I'm not sure if I have made a mistake somewhere that prevents the table from working.

    Here is the whole script:



    USE `objects`;

    -- -----------------------------------------------------
    -- Table `objects`.`objectlist`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `objects`.`objectlist` (
    `ObjectID` BIGINT NOT NULL ,
    `Name` VARCHAR(45) NOT NULL ,
    `Class` INT NULL ,
    `Designer` VARCHAR(60) NULL ,
    `Description` TEXT NULL ,
    `Rating` FLOAT NULL ,
    `Active` TINYINT(1) NULL ,
    PRIMARY KEY (`ObjectID`) )
    ENGINE = InnoDB
    COMMENT = 'This table stores a list of all the objects in the world';


    -- -----------------------------------------------------
    -- Table `Objects`.`componentlist`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `objects`.`componentlist` (
    `ComponentID` BIGINT NOT NULL ,
    `Mesh` VARCHAR(90) NULL ,
    `PhysicsIndex` INT NULL ,
    `AudioIndex` BIGINT NULL ,
    `AIScript` VARCHAR(90) NULL ,
    PRIMARY KEY (`ComponentID`) )
    ENGINE = InnoDB
    COMMENT = 'List of all components';


    -- -----------------------------------------------------
    -- Table `objects`.`childrencomponents`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `objects`.`childrencomponents` (
    `ObjectID` BIGINT NOT NULL ,
    `ComponentID` BIGINT NOT NULL ,
    `PosX` FLOAT NULL ,
    `PosY` FLOAT NULL ,
    `PosZ` FLOAT NULL ,
    `RotX` FLOAT NULL ,
    `RotY` FLOAT NULL ,
    `RotZ` FLOAT NULL ,
    `ScaleX` FLOAT NULL ,
    `ScaleY` FLOAT NULL ,
    `ScaleZ` FLOAT NULL ,
    PRIMARY KEY (`ObjectID`, `ComponentID`, `PosX`, `PosY`, `PosZ`) ,
    INDEX `ObjectID` (`ObjectID` ASC) ,
    INDEX `ComponentID` (`ComponentID` ASC) ,
    CONSTRAINT `ObjectID`
    FOREIGN KEY (`ObjectID` )
    REFERENCES `Objects`.`objectlist` (`ObjectID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `ComponentID`
    FOREIGN KEY (`ComponentID` )
    REFERENCES `objects`.`componentlist` (`ComponentID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB
    COMMENT = 'List of components associated with each object';

    -- -----------------------------------------------------
    -- Table `objects`.`childrenobjects`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `objects`.`childrenobjects` (
    `ObjectID` BIGINT NOT NULL ,
    `ChildObjectID` BIGINT NULL ,
    `PosX` FLOAT UNSIGNED NULL ,
    `PosY` FLOAT UNSIGNED NULL ,
    `PosZ` FLOAT UNSIGNED NULL ,
    `RotX` FLOAT NULL ,
    `RotY` FLOAT NULL ,
    `RotZ` FLOAT NULL ,
    `ScaleX` FLOAT NULL ,
    `ScaleY` FLOAT NULL ,
    `ScaleZ` FLOAT NULL ,
    PRIMARY KEY (`ObjectID`, `ChildObjectID`, `PosX`, `PosY`, `PosZ`) ,
    INDEX `ObjectID` (`ObjectID` ASC) ,
    CONSTRAINT `ObjectID`
    FOREIGN KEY (`ObjectID` )
    REFERENCES `objects`.`objectlist` (`ObjectID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB
    COMMENT = 'Objects that are children of other objects';


    -- -----------------------------------------------------
    -- Table `objects`.`objecttags`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `objects`.`objecttags` (
    `ObjectID` BIGINT NOT NULL ,
    `Tag` VARCHAR(100) NULL ,
    PRIMARY KEY (`ObjectID`) ,
    INDEX `ObjectID` (`ObjectID` ASC) ,
    CONSTRAINT `ObjectID`
    FOREIGN KEY (`ObjectID` )
    REFERENCES `objects`.`objectlist` (`ObjectID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB
    COMMENT = 'Stores tags for each object';


    -- -----------------------------------------------------
    -- Table `objects`.`componenttags`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `objects`.`componenttags` (
    `ComponentID` BIGINT NOT NULL ,
    `Tag` VARCHAR(100) NULL ,
    PRIMARY KEY (`ComponentID`) ,
    INDEX `ComponentID` (`ComponentID` ASC) ,
    CONSTRAINT `ComponentID`
    FOREIGN KEY (`ComponentID` )
    REFERENCES `objects`.`componentlist` (`ComponentID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB
    COMMENT = 'Stores tags for each component';



    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make sure each of your foreign keys, across all tables, has a unique constraint name

    currently you are assigning the constraint name ObjectID more than once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •