Could not get relations working

[Lei Zhao]

http://squeryl.org/relations.html

I failed to commit the relation into database. However, `Book.printDdl` did print `alter table Book add constraint BookFK1 foreign key (AUTHOR_ID) references AUTHORS(id) on delete cascade`. I went into MySQL and found there was no constraint. This constraint was neither enforced by the squeryl, I can inserted item successfully into DB that apparently violates the constraint.

I've put my code here. https://github.com/LeeiFrankJaw/dir2db I hope someone can provide some insight.

[Lei Zhao]

I made some progress on this. Now I can confirm the `alter table` was not executed.  I set up the logger the according to http://squeryl.org/miscellaneous.html .

    Session.currentSession.setLogger(println)
    Library.create
    Library.printDdl

The output of `Library.create` differs from that of `Library.printDdl` in that it doesn't have the `alter table` statement.

[Lei Zhao]

It seems that MySQL is not supported for foreign key, as shown below.

https://github.com/squeryl/squeryl/blob/0.9.5-7/src/main/scala/org/squeryl/adapters/MySQLAdapter.scala#L73

  /**

   *

   * Foreign key constraints are not supported,

   *

   *  MySQL has some pre requisites for creating a foreign key constraint

   *  one of which is :

   *

   *  -> The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.

   *

   *  http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

   *

   *  Apparently there are other pre requisites, because creating foreign key constraints still gives :

   *

   * Time Action Response Duration / Fetch

   *  0 1 18:26:25 alter table CourseSubscription add constraint CourseSubscriptionFK3

   *  foreign key (courseId) references Course(id) Error Code: 1005

   *  Can't create table 'test.#sql-57c_42' (errno: 150)

   *

   * 

   *  http://bytes.com/topic/mysql/answers/865699-cant-create-table-errno-150-foreign-key-constraints

   *

   * 

   */

[Lei Zhao]

I replaced `MySQLAdapter` with `MySQLInnoDBAdapter`  and it works now and creates foreign key constraints for me.