auto_migrate! with belongs_to with a composite key works in one case but not the other

[datamapper]

auto_migrate! with belongs_to with a composite key works in one case but not the other

[solnic]

Could you check this against DM gems from master?

[jdodds]

Just did, and updated the repo I made to reflect that. I see the same behavior.

Here's the relevant info from the logs:

[datamapper] Setting up the "development" environment:
[datamapper] Setting up :default repository: 'datamapper_bugs_development' on mysql
  SQL (0.096ms)  SET sql_auto_is_null = 0
  SQL (0.136ms)  SET SESSION sql_mode = 'ANSI,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,TRADITIONAL'
  SQL (0.282ms)  SHOW TABLES LIKE 'backup_picks'
  SQL (0.186ms)  SHOW TABLES LIKE 'picks'
  SQL (0.180ms)  DROP TABLE IF EXISTS `backup_picks`
  SQL (0.192ms)  SHOW TABLES LIKE 'backup_picks'
  SQL (12.310ms)  CREATE TABLE `backup_picks` (`preference` INTEGER NOT NULL, `person_id` INT(10) UNSIGNED NOT NULL, `user_team_user_id` INT(10) UNSIGNED NOT NULL, `user_team_league_id` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`person_id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
  SQL (8.652ms)  CREATE INDEX `index_backup_picks_person` ON `backup_picks` (`person_id`)
  SQL (9.633ms)  CREATE INDEX `index_backup_picks_user_team` ON `backup_picks` (`user_team_user_id`, `user_team_league_id`)
  SQL (0.150ms)  DROP TABLE IF EXISTS `picks`
  SQL (0.209ms)  SHOW TABLES LIKE 'picks'
  SQL (2.881ms)  CREATE TABLE `picks` (`user_team_user_id` INT(10) UNSIGNED NOT NULL, `user_team_league_id` INT(10) UNSIGNED NOT NULL, `person_id` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`user_team_user_id`, `user_team_league_id`, `person_id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
  SQL (9.370ms)  CREATE INDEX `index_picks_user_team` ON `picks` (`user_team_user_id`, `user_team_league_id`)
  SQL (8.308ms)  CREATE INDEX `index_picks_person` ON `picks` (`person_id`)
  SQL (0.174ms)  DROP TABLE IF EXISTS `user_teams`
  SQL (0.234ms)  SHOW TABLES LIKE 'user_teams'
  SQL (3.064ms)  CREATE TABLE `user_teams` (`name` VARCHAR(50) NOT NULL, `user_id` INT(10) UNSIGNED NOT NULL, `league_id` INT(10) UNSIGNED NOT NULL, PRIMARY KEY(`user_id`, `league_id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci
  SQL (8.725ms)  CREATE INDEX `index_user_teams_user` ON `user_teams` (`user_id`)
  SQL (8.333ms)  CREATE INDEX `index_user_teams_league` ON `user_teams` (`league_id`)
  SQL (0.405ms)  SELECT COUNT(*) FROM `information_schema`.`table_constraints` WHERE `constraint_type` = 'FOREIGN KEY' AND `table_schema` = 'datamapper_bugs_development' AND `table_name` = 'backup_picks' AND `constraint_name` = 'backup_picks_user_team_fk'
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') REFERENCES `user_teams` () ON DELETE NO ACTION ON UPDATE NO ACTION' at line 1 (code: 1064, sql state: 42000, query: ALTER TABLE `backup_picks` ADD CONSTRAINT `backup_picks_user_team_fk` FOREIGN KEY () REFERENCES `user_teams` () ON DELETE NO ACTION ON UPDATE NO ACTION, uri: mysql:root:FIDDLEFADDLEIBEENCENSORED@localhostdatamapper_bugs_development?database=datamapper_bugs_development&path=datamapper_bugs_development&adapter=mysql&host=localhost&username=root&password=OHGAWDTHECENSORSHIP)

[BinaryPaean]

Is there a workaround for this aside from not using composite keys on join models? I'm running into it as well.

[drydevelopment]

Here's a solution I found. Add the attribute :min => 1 to your foreign key dependent propery.

property :invoice_id, Integer, :min => 1, :index => true

http://datamapper.lighthouseapp.com/projects/20609/tickets/1236-sql-error-cant-create-table-during-migration-with-dm-constraints