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?

"fails" is not descriptive enough for us to troubleshoot adequately

try creating the 2nd table without the foreign key

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;

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