FK naming conflicts when table is many end of multiple one-to-many relations in migrations.

[GoogleCodeExporter]

This bug exists in revision 523.

If you create a migration that tries to create the following structure:

class _001_InitialDB : Migration
{
    public override void Up()
    {
        Table User = CreateTableWithKey("User", "Id");

        Table Reseller = CreateTableWithKey("Reseller", "Id");
        Reseller.AddColumn("UserId", DbType.Int32);
        CreateForeignKey(User.GetColumn("Id"), Reseller.GetColumn("UserId"));

        Table Account = CreateTableWithKey("Account", "Id");
        Account.AddColumn("UserId", DbType.Int32);
        CreateForeignKey(User.GetColumn("Id"), Account.GetColumn("UserId"));
    }
}

The following exception is thrown on migration:
There was an error running migration (001_InitialDB):
 There is already an object named 'fk_User_Id_UserId' in the database.

This seems to be caused by the naming convention for FKs in
ANSISqlGenerator:777:

string fkName = string.Format("fk_{0}_{1}_{2}", oneTable.Table.Name,
oneTable.ColumnName, manyTable.ColumnName);

I suggest changing it to:

string fkName = string.Format("FK_{0}_{1}__{2}_{3}", manyTable.Table.Name,
manyTable.ColumnName, oneTable.Table.Name, oneTable.ColumnName);

on line 777 and 794.

This also aligns better with the default SQL Server naming convention.

Original issue reported on code.google.com by wjb...@gmail.com on 22 Jun 2009 at 3:14

[GoogleCodeExporter]

Adding patch.

Original comment by wjb...@gmail.com on 22 Jun 2009 at 3:27

Attachments:

• foreignkeynaming.patch

[GoogleCodeExporter]

I started a discussion about that a while ago here:
http://groups.google.com/group/subsonicdev/browse_thread/thread/9f515ab3d0fc305

Because of a lack of time I ignored it and manipulated the generated sql before
running it against my db. But today I encountered the problem again and decided 
to
patch that. But as you have already done it...

thx.

Original comment by j.steinblock@gmail.com on 30 Jun 2009 at 6:42