I don't get relationships when importing from MySQL 5.1.44

Hi -

I love the way this works when connected to SQL Server, but can't seem to get it to work when using MySQL 5.  I created a set of tables and relationships in the MySQL design tool, using the InnoDB database engine, checked to make sure the relationships actually existed in the database, and then dragged them to the VS2008/Lightspeed design space.  The tables are created OK, but the foreign key fields are not visible and no relationships are created.

I know this works, what might I be doing wrong?

I've run a quick check here and it seems to be working for me.  From the fact that the FK fields aren't visible, LightSpeed has obviously figured out that there is meant to be an association there, so I'm not sure why it wouldn't go ahead and create that association.  If you have the Output window open when you do the drag-drop, do any warning messages appear?  Could you post the CREATE TABLE statements for a couple of the tables that are giving you trouble?  Thanks!

OK, I tried a smaller database with 2 linked tables, and it worked fine.

Then, I tried deleting several of the relationships and only importing 2 tables that were not related to anything else -- no luck.

Here's the SQL for the 2 tables:

CREATE  TABLE IF NOT EXISTS `hawk`.`devices` (
  `DeviceID` INT(11) NOT NULL AUTO_INCREMENT ,
  `DeviceName` VARCHAR(45) NOT NULL ,
  `SerialNumber` VARCHAR(20) NOT NULL ,
  `Seq` TINYINT NOT NULL ,
  PRIMARY KEY (`DeviceID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1

CREATE  TABLE IF NOT EXISTS `hawk`.`fields` (
  `FieldID` INT(11) NOT NULL AUTO_INCREMENT ,
  `NiceName` VARCHAR(45) NOT NULL ,
  `Value` VARCHAR(45) NOT NULL ,
  `Min` VARCHAR(45) NOT NULL ,
  `Max` VARCHAR(45) NOT NULL ,
  `PassFail` ENUM('P','F') NOT NULL ,
  `DeviceID` INT(11) NOT NULL ,
  `Seq` TINYINT NOT NULL ,
  PRIMARY KEY (`FieldID`) ,
  INDEX `FK_DeviceID` (`DeviceID` ASC) ,
  CONSTRAINT `FK_DeviceID`
    FOREIGN KEY (`DeviceID` )
    REFERENCES `hawk`.`devices` (`DeviceID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1

Question - does the primary ID have to start with Id?  My convention has always been to name the primary key as RecordName + ID, for example the Devices table pk is DeviceID.  Could that have anything to do with it?

The ID doesn't have to be called Id, provided it's the primary key: your naming convention is fine because you do declare the PK.  (If you didn't specify a PK then it would have to be called Id for the designer to recognise it during drag and drop, though you could still fix it up by hand afterwards.)

I've tried dragging those tables onto the designer and I get the relationship created correctly.  My test server is MySQL 5.1.32 though so I guess it might be an issue with later versions of MySQL.  What happens if you create just those two tables in a fresh database?  Does it work for you then?  If you then add the other tables from your "real" database to that fresh database, does it then start failing?  I'm wondering if there are other FKs defined on other tables and we're picking them up by mistake...