SubSonic isn't generating MySql foreign key tables

Question

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;

Answer 1

I'm no subsonic expert, but I wonder if there may be a technical violation of one rule, in your syntax, that perhaps confuses subsonic: according to MySQL's reference,

If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database.

You're naming both the CONSTRAINT and the KEY index FK_Child -- intuitively that makes sense and no doubt MySQL and InnoDB are happy with it, but maybe subsonic's parsing that restriction differently and more restrictively. What happens if you rename the constraint to avoid the technical "conflict" of names between it and the key index?

Answer 2

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

Answer 3

1. use InnoDB tables
2. use SubSonic.MySqlInnoDBDataProvider instead of SubSonic.MySqlDataProvider !! (otherwise subsonic will silently ignore your FKs)
3. define primary key for your tables (don't need to be unique)
4. be sure, that your table has FKs defined (mysql silently ignores FK creation if you have incorrectly setup your db or table, mainly due to point 1.) use: show create table to be sure

hope this helps...

Answer 4

I'm seeing the same behavior now with SubSonic 3.0.0.4 - I'm guessing it's the same issue, but I can't figure out where in the MySQL.ttinclude or other T4 templates to set this... Any ideas?