Found the problem...........

Even though the tool has an option to omit schema names, it put one in the REFERENCES for the Foreign Key.


>Working in MySQL we are trying to create the following.....
>
>A Table named "ShiftType" with a primary key of IdShiftType
>
>A Table named "Shift" with a field name "IdShiftType" where each entry must match a record in the "ShiftType" table.
>
>We used a tool to develop a script that generated the table definitions. It produced the following

>-- -----------------------------------------------------
>-- Table `ShiftType`
>-- -----------------------------------------------------
>DROP TABLE IF EXISTS `ShiftType` ;
>
>SHOW WARNINGS;
>CREATE  TABLE IF NOT EXISTS `ShiftType` (
>  `idShiftType` CHAR(10) NOT NULL ,
>  `Name` CHAR(15) NOT NULL ,
>  `Description` VARCHAR(50) NULL ,
>  PRIMARY KEY (`idShiftType`) )
>ENGINE = InnoDB;
>
>SHOW WARNINGS;
>
>-- -----------------------------------------------------
>-- Table `Shift`
>-- -----------------------------------------------------
>DROP TABLE IF EXISTS `Shift` ;
>
>SHOW WARNINGS;
>CREATE  TABLE IF NOT EXISTS `Shift` (
>  `idShift` CHAR(10) NOT NULL ,
>  `IDShiftType` CHAR(10) NOT NULL ,
>         (more fields),
>  PRIMARY KEY (`idShift`) ,
>  CONSTRAINT `shifttype`
>    FOREIGN KEY (`IDShiftType` )
>    REFERENCES `mydb`.`ShiftType` (`idShiftType` )
>    ON DELETE NO ACTION
>    ON UPDATE NO ACTION)
>ENGINE = InnoDB;
>
>SHOW WARNINGS;
>CREATE INDEX `shifttype` ON `Shift` (`IDShiftType` ASC) ;

>
>I've added three records to ShiftType

> idShiftType     Name      Description
>0000000001     One        First Description
>0000000002     Two        Second Description
>0000000003     Three      Third Description

>
>If I now issue

>INSERT INTO shift (idShift,IdShiftType) VALUES ("0000000001","0000000001")

>We get an error message
>

Cannot add or update a child row: a foreign key constraint fails
> (`rlp_spotlight/shift`, CONSTRAINT `shifttype` 
>FOREIGN KEY (`IDShiftType`) REFERENCES `mydb`.`shifttype` (`idShiftType`) ON DELETE NO ACTION ON UPDATE NO ACTION)

>
>Any information on what we are doing wrong will be greatly appreciated
>
>.............Rich