Problem with migrations in column unique and auto-increment using MySQL

Question

I have a problem when I use the MySQL database in my project. In general, some project tables need to have an auto-incrementing field as data is entered. However, in MySQL a column except Id can only be auto-incrementing if it is unique key. For example:

public class Client
{
    [Key]
    public Guid Id { get; set; }

    [MaxLength(200)]
    public string Name { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Code { get; set; }

    public DateTime BirthDate { get; set; }
}

However, in MySQL a column except Id can only be auto-incrementing if it is unique key. For example:

modelBuilder.Entity<Client>()
    .HasIndex(c => c.Code)
    .IsUnique();

So far so good. The Code is correct and compiling. However, when generating migrations, the result is:

Part 1:

migrationBuilder.CreateTable(
    name: "Client",
    columns: table => new
    {
        Id = table.Column<Guid>(nullable: false),
        Code = table.Column<int>(nullable: false)
            .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
        Name = table.Column<string>(maxLength: 200, nullable: false),
        BirthDate = table.Column<DateTime>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Client", x => x.Id);
    }
);

Part 2:

migrationBuilder.CreateIndex(
    name: "IX_Client_Code",
    table: "Client",
    column: "Code",
    unique: true);

Ao executar a atualização do banco de dados (database update) a seguinte mensagem é exibida: Incorrect table definition; there can be only one auto column and it must be defined as a key.

This error is caused by the way migrations is created. In part one of the code above it is already explicit that "Code" is MySqlValueGenerationStrategy.IdentityColumn and so the error happens. To solve this problem, I had to create two migrations: the first adds only the Code field to single and the second inserts the auto increment. However, I would not want to use it this way, because every time I would have to create at least two migrations.

NOTE: I could give up the GUID `` and only use Code (int) for tables with this scenario, but this is not possible because I would have to modify the structure of all tables. Also, another possible solution I found was to make ID and Code the primary key, but I'm not yet likely to use it that way.

Answer 1

There are two ways to resolve this.

If you just want to manually fix the migration code, then do not use CreateIndex, but just add the alternate key to the constraints of the create table operation:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "Client",
        columns: table => new
        {
            Id = table.Column<Guid>(nullable: false),
            Name = table.Column<string>(maxLength: 200, nullable: true),
            Code = table.Column<long>(nullable: false)
                .Annotation("MySql:ValueGenerationStrategy",
                            MySqlValueGenerationStrategy.IdentityColumn),
            BirthDate = table.Column<DateTime>(nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Client", x => x.Id);
            table.UniqueConstraint("AK_Client", x => x.Code); // <-- Add unique constraint
        });
}

There is not data annotation to define an alternate key, but you can use the fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Client>(entity => entity.HasAlternateKey(e => e.Code));
}

This will create the same working migration code as above and would result in the following CREATE TABLE statement:

CREATE TABLE `Client` (
    `Id` char(36) NOT NULL,
    `Name` varchar(200) CHARACTER SET utf8mb4 NULL,
    `Code` bigint NOT NULL AUTO_INCREMENT,
    `BirthDate` datetime(6) NOT NULL,
    CONSTRAINT `PK_Client` PRIMARY KEY (`Id`),
    CONSTRAINT `AK_Client` UNIQUE (`Code`)
);

Using HasIndex().IsUnique() will not work, as this will generate a CreateIndex() call with the same problem you described in your question.