Clever Geek Handbook

An error when starting Update-Database telling me it cannot create more than one clustered index on a table

I'm just doing some extra work and developing a fairly basic Azure Mobile Service. I am trying to enable Migrations in a project, but I am facing an unusual problem - I believe I am doing something wrong.

All my models inherit the Microsoft.WindowsAzure.Mobile.Service.EntityData class as suggested by all the tutorials regarding mobile services. When I enable Migration, add an initial migration and try to apply it by running

Update-Database

I am getting the error in the console:

Cannot create more than one clustered index on table 'XXX.YYY'. Drop the existing clustered index 'PK_XXX.YYY' before creating another.

When we check the documentation ( here ), we can find information that an azure sql database can only have one clustered index. Also, when you add a primary key, it will automatically add one clustered index for itself. This is normal. But then why does EntityData look like this:

public abstract class EntityData : ITableData
{
    protected EntityData();

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Index(IsClustered = true)]
    [TableColumn(TableColumnType.CreatedAt)]
    public DateTimeOffset? CreatedAt { get; set; }

    [TableColumn(TableColumnType.Deleted)]
    public bool Deleted { get; set; }

    [Key]
    [TableColumn(TableColumnType.Id)]
    public string Id { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    [TableColumn(TableColumnType.UpdatedAt)]
    public DateTimeOffset? UpdatedAt { get; set; }

    [TableColumn(TableColumnType.Version)]
    [Timestamp]
    public byte[] Version { get; set; }
}

As we can see, we have an Id property declaration that should be the primary key + a CreatedAt property declaration with a clustered index. When I change the generated migration code and remove, for example, the clustered index from CreatedAt, everything works fine. But then, next time I add a new migration, it will probably find the difference again and try to add that index. I would like to avoid this.

I would be grateful for advice, advice and help!

Tank you!

Darek

+3


source to share


1 answer


Ah ... I found out another question that helped me solve the problem.

here is the original question and here is an article with more details on correct migration procedure.

Typically, you need to slightly modify the WebApiConfig implementation:



  • remove Database.SetInitializer(..)

    call from Register()

    function

  • instead of these lines:

    var migrator = new DbMigrator(new Configuration()); migrator.Update();

Now after starting the application everything works fine. Also the command Update-Database

works as expected.

+3


source






More articles:


All Articles