allow autoincrement column other than primary key

[kvanska]

Steps to reproduce

Model:

public class Area
{        
        [Key]
        public Guid AreaId { get; set; }
        
        [Column("auto_id")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long AutoId { get; set; }
}

Builder:

// unique index to auto_id
builder.HasIndex(a => a.AutoId).IsUnique();

The issue

when creating migration, migration is created ok but when updating database, exception:

Incorrect table definition; there can be only one auto column and it must be defined as a key

To make it work:

1. leave [DatabaseGenerated(DatabaseGeneratedOption.Identity)] attribute away
2. create migration and update dabase
3. add [DatabaseGenerated(DatabaseGeneratedOption.Identity)] attribute
4. create migration and update database

Further technical details

MySQL version: 5.7.23 Operating system: macOS High Sierra Pomelo.EntityFrameworkCore.MySql version: 2.1.2 Other details about my project setup:

[mguinness]

Can you Generate SQL scripts? Is the unique index specified in CREATE TABLE or ALTER TABLE?

[RatserX]

Any update on this? I'm having the same problem.

Further technical details

MySQL version: 5.7.23 (WampServer) Operating system: Windows 10 Pomelo.EntityFrameworkCore.MySql version: 2.1.4 Other details about my project setup: .NET Core 2.1 Microsoft EntityFrameworkCore 2.1,11

UPDATE (SOLVED)

Just add the Unique attribute to the autogenerated column using the model builder with HasAlternateKey instead of IsUnique. e.g.

modelBuilder.Entity<Area>()
    .Property<int>("AutoId")
    .ValueGeneratedOnAdd(); // Autogenerated
modelBuilder.Entity<Area>()
    .HasAlternateKey("AutoId"); // Unique

[luchomejia]

I cant get the Auto Increment to be created in my migrations in a non-primary Key column.

Tried this:

      [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
      public int OrgNumber {get; set;}

Also tried this:

   builder.Entity<Customer>()
                .Property<int>("OrgNumber")
                .ValueGeneratedOnAdd();
   builder.Entity<Customer>()
                .HasAlternateKey("OrgNumber");

None of that worked for me. Ran the migrations with previous data in the db, and also with an empty database, still no AI in the column.

I have another column in the table which is the primary key, called Id and it is a Guid.

Migration code looks like this

migrationBuilder.AddColumn<int>(
                name: "OrgNumber",
                table: "Customer",
                nullable: false,
                defaultValue: 0)
                .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);

migrationBuilder.AddUniqueConstraint(
                name: "AK_Customer_OrgNumber",
                table: "Customer",
                column: "OrgNumber");

Any ideas?? @caleblloyd @lauxjpn Appreciate any help on this, for now, had to put it manually in the db. but I dont want to have to do that when deploying to other environments.

[mguinness]

If you look at the code for migrations you'll see conditions for appending AUTO_INCREMENT - do you know why column has a default value of zero?

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/a7a03b17fc6496f000d88471f376a1901d293e7d/src/EFCore.MySql/Migrations/MySqlMigrationsSqlGenerator.cs#L800-L826

[lauxjpn]

As @mguinness correctly stated, the auto increment is not applied due to the default value.

The following sample shows, that without a default value, a single auto increment column can be used, if the column is ~~indexed~~ a unique key (or primary key):

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public Guid IceCreamId { get; set; }
        public string Name { get; set; }
        public int AutoGeneratedUniqueId { get; set; }
    }

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql(
                    "server=127.0.0.1;port=3306;user=root;password=;database=Issue711",
                    b => b.ServerVersion("8.0.20-mysql"))
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>(
                entity =>
                {
                    entity.HasKey(e => e.IceCreamId);
                    entity.HasAlternateKey(e => e.AutoGeneratedUniqueId);
                    
                    entity.Property(e => e.AutoGeneratedUniqueId)
                        .ValueGeneratedOnAdd();
                    
                    entity.HasData(
                        new IceCream
                        {
                            IceCreamId = Guid.NewGuid(),
                            Name = "Vanilla",
                            AutoGeneratedUniqueId = 1
                        },
                        new IceCream
                        {
                            IceCreamId = Guid.NewGuid(),
                            Name = "Chocolate"
                            // <-- we let AUTO_INCREMENT handle the ID generation here
                        }
                    );
                });
        }
    }

    internal class Program
    {
        private static void Main()
        {
            using (var context = new Context())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                var iceCreams = context.IceCreams
                    .OrderBy(i => i.Name)
                    .ToList();
                
                Debug.Assert(iceCreams.Count == 2);
                Debug.Assert(iceCreams[0].AutoGeneratedUniqueId == 2);
                Debug.Assert(iceCreams[1].AutoGeneratedUniqueId == 1);
            }
        }
    }
}

It generates the following SQL:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE DATABASE `Issue711`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (86ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE TABLE `IceCreams` (
          `IceCreamId` char(36) NOT NULL,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `AutoGeneratedUniqueId` int NOT NULL AUTO_INCREMENT,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`),
          CONSTRAINT `AK_IceCreams_AutoGeneratedUniqueId` UNIQUE (`AutoGeneratedUniqueId`)
      );

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      INSERT INTO `IceCreams` (`IceCreamId`, `AutoGeneratedUniqueId`, `Name`)
      VALUES ('f245451b-e6fb-43fc-8697-3a4c29116a78', 1, 'Vanilla');

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
      VALUES ('aa2bd2da-a209-4987-af32-3235f8e5aa8e', 'Chocolate');

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      SELECT `i`.`IceCreamId`, `i`.`AutoGeneratedUniqueId`, `i`.`Name`
      FROM `IceCreams` AS `i`
      ORDER BY `i`.`Name`

[luchomejia]

Thanks for the quick reply guys, That seems to be the issue. Not sure why it is generating that default value in the migration code as I don't have any additional attributes in the entity property nor in the model builder.

I was just running:

dotnet ef migrations add MigrationName

I will be updating the migration manually later today to remove the default and will let you know how it goes.

[lauxjpn]

Not sure why it is generating that default value in the migration code as I don't have any additional attributes in the entity property nor in the model builder.

Actually, this seems to be an EF Core feature, that makes sense in most cases. If you add a new column to an already existing table, and that column is not nullable, then you need to update all existing rows with some initial value for the new column, which is being done here by using a default value.

Unfortunately in the case of using AUTO_INCREMENT, this feature will lead to unexpected results.

We should change our handling of default values and MySqlValueGenerationStrategy.IdentityColumn, to support this scenario.

There is no default value needed for auto increment columns, because existing rows automatically get updated with an incrementing value, when the column gets added:

drop database if exists `Issue711_01`;
create database `Issue711_01`;
use `Issue711_01`;

CREATE TABLE `TestTable` (
  `IceCreamId` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`IceCreamId`)
);

insert into `TestTable` (`IceCreamId`) values ('VANILLA');
insert into `TestTable` (`IceCreamId`) values ('CHOCOLATE');

alter table `TestTable`
	add column `AutoId` int not null auto_increment,
	add constraint unique index `AK_AutoId` (`AutoId` asc);

select * from `TestTable`;

Result:

IceCreamId	AutoId
CHOCOLATE	1
VANILLA	2

[cathei]

I tested it without default constraint, but I'm getting this error

MySqlException: Incorrect table definition; there can be only one auto column and it must be defined as a key

On MySQL 5.6.48

So the column must be a primary key..

[luchomejia]

Got the same, it needs to somehow be a Key, It does not need to be Primary Key, Unique key will work, but since Unique key will run after the Add Column part is ran, I still get the error.

MySqlException: Incorrect table definition; there can be only one auto column and it must be defined as a key

I can get it to work if I delete all migrations and create one from scratch, but if the column needs to be added later that would be a problem.

If I do it as a second migration the code goes like this:

  migrationBuilder.AddColumn<int>(
                name: "OrgNumber",
                table: "Customer",
                nullable: false)
                .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);

   migrationBuilder.AddUniqueConstraint(
                name: "AK_Customer_OrgNumber",
                table: "Customer",
                column: "OrgNumber");

This causes the script to be ran in 2 separate moments. But the first one would fail.

Is there a way to combine those two things in a single piece of code so only one script rusn and the column gets added together with the constraint?

Something like:

alter table `Customer`
	add column `OrgNumber` int not null auto_increment,
	add constraint unique index `AK_Customer_OrgNumber` (`OrgNumber` asc);

[cathei]

One of possible workarounds for now is using custom operation on migration script. https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/operations

I've got to work by adding this line and commenting out AddColumn and AddUniqueConstraint:

migrationBuilder.Sql("ALTER TABLE `Area` ADD `AutoId` int AUTO_INCREMENT UNIQUE;");

[afsharm]

I have similar issue with Microsoft.EntityFrameworkCore 5.0.11 and Pomelo.EntityFrameworkCore.MySql 5.0.2 with following code:

public class Car
{
    public Guid CarId { get; set; }
    public string CarName { set; get; }
    public int CarNumber { set; get; }
}

public class CarTypeConfiguration : IEntityTypeConfiguration<Car>
{
    public void Configure(EntityTypeBuilder<Car> builder)
    {
        builder.HasKey(x => x.CarId);
        builder.Property(x => x.CarNumber).ValueGeneratedOnAdd();
    }
}

20220217153551_AddCarNumber.cs

migrationBuilder.AddColumn<int>(
    name: "CarNumber",
    table: "car",
    type: "int",
    nullable: false,
    defaultValue: 0)
    .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);

20220217153551_AddCarNumber.Designer.cs

modelBuilder.Entity("MyNameSpace.Car", b =>
    {
        b.Property<Guid>("CarId")
            .ValueGeneratedOnAdd()
            .HasColumnType("char(36)");

        b.Property<int>("CarNumber")
            .ValueGeneratedOnAdd()
            .HasColumnType("int");

        b.HasKey("CarId");

        b.ToTable("Car");
    });

DDL

CREATE TABLE `Car` (
  `CarId` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `CarNumber` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `Car`
  ADD PRIMARY KEY (`CarId`);

Using alternate key via builder.HasAlternateKey(x => x.CarNumber); did not work also. It did not add AUTO_INCREMENT to the generated SQL in the database.

I have also made a question on Stack Overflow with more details at EF Core MySQL add auto increment field.

[lauxjpn]

@afsharm Did you try @cathei's workaround? It should work:

One of possible workarounds for now is using custom operation on migration script. https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/operations

I've got to work by adding this line and commenting out AddColumn and AddUniqueConstraint:

migrationBuilder.Sql("ALTER TABLE `Area` ADD `AutoId` int AUTO_INCREMENT UNIQUE;");

[DanielePetraliaDev]

I had the same problem and solved by doing two different migrations. Starting from not having anything set up for AI:

1. Adding builder.Entity<YourEntity>().HasIndex(e => e.yourAiField); in OnModelCreating and updating the database
2. Adding builder.Entity<YourEntity>().Property("yourAiField").ValueGeneratedOnAdd(); and updating the database

Looks like it's working.