Mysql – SubSonic isn’t generating MySql foreign key tables

foreign-keysMySQLsubsonic

I two tables within a MySql 5.1.34 database. When using SubSonic to generate the DAL, the foreign-key relationship doesn't get scripted, ie; I have no Parent.ChildCollection object. Looking inside the generated DAL Parent class shows the following;

//no foreign key tables defined (0)

I have tried SubSonic 2.1 and 2.2, and various MySql 5 versions. I must be doing something wrong procedurally – any help would be greatly appreciated. This has always just worked 'out-the-box' when using MS-SQL. 

TABLE `parent` (
  `ParentId` INT(11) NOT NULL AUTO_INCREMENT,
  `SomeData` VARCHAR(25) DEFAULT NULL,
  PRIMARY KEY (`ParentId`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

TABLE `child` (
  `ChildId` INT(11) NOT NULL AUTO_INCREMENT,
  `ParentId` INT(11) NOT NULL,
  `SomeData` VARCHAR(25) DEFAULT NULL,
  PRIMARY KEY (`ChildId`),
  KEY `FK_child` (`ParentId`),
  CONSTRAINT `FK_child` FOREIGN KEY (`ParentId`) REFERENCES `parent` (`ParentId`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

Best Answer

It works for me with this settings.

  <add name="ProviderName"
       type="SubSonic.MySqlInnoDBDataProvider, SubSonic"
       connectionStringName="ConnectionString"
       generateLazyLoads="true"
       generatedNamespace="My.NameSpace"
       generateRelatedTablesAsProperties="true"
       tableBaseClass="ActiveRecord" />

Subsonic 2.2 and MySql 5.1.30. You should also check if both tables are MyISAM.

And did you just create this foreign key? Then it's likely that Subsonic doesn't notice your changes, because MySQL seems to cache the Tableschema. See: http://code.google.com/p/subsonicproject/issues/detail?id=87

Related Topic