schambers/fluentmigrator — Issue #242: calling .References against MySql doesn't work
Author: worldspawn
Created: 2012-02-14 00:52:44+00:00
State: open

I am trying to create a new table with a self referencing column.

.WithColumn("ParentId").AsGuid().Nullable().References("FK_Site_Site", "Site", new [] {"Id" })

This generates:
'''
-- CreateTable Site
CREATE TABLE `Site` (`Id` VARCHAR(40) NOT NULL, `Version` INTEGER NOT NULL, `Name` VARCHAR(255) NOT NULL, `ParentId` VAR
CHAR(40), `ExternalId` VARCHAR(128) NOT NULL, `AdServerId` VARCHAR(8) NOT NULL, `ClientId` VARCHAR(40) NOT NULL, `Invent
oryLabel` VARCHAR(64) NOT NULL, CONSTRAINT `PK_Site` PRIMARY KEY (`Id`)) ENGINE = INNODB
-- -> 0.0020002s

-- CreateForeignKey FK_Site_Site Site(Id) Site(ParentId)
ALTER TABLE `Site` ADD CONSTRAINT `FK_Site_Site` FOREIGN KEY (`Id`) REFERENCES `Site` (`ParentId`)
'''

The ALTER TABLE fails to execute. Looking it it appears around the wrong way.

I would have expected

'''
ALTER TABLE `Site` ADD CONSTRAINT `FK_Site_Site` FOREIGN KEY (`ParentId`) REFERENCES `Site` (`Id`)
'''

Elsewhere I have this migration:

'''
Create.ForeignKey("FK_HouseCampaignKeys_HouseCampaign")
.FromTable("HouseCampaignKeys").ForeignColumn("HouseCampaignId")
.ToTable("HouseCampaign").PrimaryColumn("Id");
'''

'''
-- CreateForeignKey FK_HouseCampaignKeys_HouseCampaign HouseCampaignKeys(HouseCampaignId) HouseCampaign(Id)
ALTER TABLE `HouseCampaignKeys` ADD CONSTRAINT `FK_HouseCampaignKeys_HouseCampaign` FOREIGN KEY (`HouseCampaignId`) R
RENCES `HouseCampaign` (`Id`)
-- -> 0.0040002s
'''

Which works and is what I was expecting. Is "References" broken for mysql or I am just looking at this wrong?