Deviation v8 vs v9: Creation of UNIQUE constraint on AUTO_INCREMENT column part of composite primary

[fehckru]

Describe the bug
Version 9 produces a UNIQUE constraint on a column with AUTO_INCREMENT that is part of a composite primary key, where version 8 did not.

To Reproduce
Entities

public class Parent
{
    [Key]
    public Guid Id { get; set; }
    
    public required ICollection<Child> Children { get; init; }
}

public record Child
{
    public int Order { get; init; }
}

EF Config

  EntityTypeBuilder<Parent> parent = builder.Entity<Parent>();
  parent.ToTable("Parent");
  
  parent.OwnsMany(
      bmc => bmc.Children,
      ing =>
      {
          ing.ToTable("Child");
          ing.HasKey("ParentId", "Order");
      }
  );

Version 8 created the following SQL:

      CREATE TABLE `Child` (
          `Order` int NOT NULL AUTO_INCREMENT,
          `ParentId` char(36) COLLATE ascii_general_ci NOT NULL,
          CONSTRAINT `PK_Child` PRIMARY KEY (`Order`, `ParentId`),
          CONSTRAINT `FK_Child_Parent_ParentId` FOREIGN KEY (`ParentId`) REFERENCES `Parent` (`Id`) ON DELETE CASCADE
      ) CHARACTER SET=utf8mb4;

Version 9 created the following SQL:

      CREATE TABLE `Child` (
          `Order` int NOT NULL AUTO_INCREMENT,
          `ParentId` char(36) COLLATE ascii_general_ci NOT NULL,
          CONSTRAINT `PK_Child` PRIMARY KEY (`ParentId`, `Order`),
          UNIQUE (`Order`),
          CONSTRAINT `FK_Child_Parent_ParentId` FOREIGN KEY (`ParentId`) REFERENCES `Parent` (`Id`) ON DELETE CASCADE
      ) CHARACTER SET=utf8mb4;

Expected behavior
Both versions should produce the same SQL, i.e. v9 should not create a UNIQUE constraint.
Sidenote: The order of columns in the composite primary is also reversed.

Technical details:

• Database server version: MariaDB (mariadb:10.11@sha256:24bb6e3d8e46f4581ddcc2b2c22fd78c99c77ac4e3fb7c9aba3a5c4c4d934ca2)
• Operating system: Win 11
• Pomelo.EntityFrameworkCore.MySql version: v8 - 8.0.3 | v9 - 9.0.0

[PaulKVCare]

We discovered the same issue today after upgrading from 8.0.3 to 9.0.0.

v8.0.3 generates this

CREATE TABLE `SomeTable` (
    `ID` bigint NOT NULL AUTO_INCREMENT, 
    `DateTime` datetime(6) NOT NULL,
    CONSTRAINT `PK_SomeTable` PRIMARY KEY (`ID`, `DateTime`)
)

v9.0.0 generates this

CREATE TABLE `SomeTable` (
    `ID` bigint NOT NULL AUTO_INCREMENT, 
    `DateTime` datetime(6) NOT NULL,
    CONSTRAINT `PK_SomeTable` PRIMARY KEY (`DateTime`, `ID`),
    UNIQUE (`ID`)
)

For a record class defined as:

[PrimaryKey(nameof(DateTime), nameof(ID))]
public abstract class SomeRecord
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity), Required]
    public long ID { get; set; }

    [Required]
    public required DateTimeOffset DateTime { get; set; }
}

In this case migration script in v9.0.0. Generates both the primary key and a Unique key for ID. For v8.0.3 it generates only the primary key.

One additional observation. changing the primary key to.
[PrimaryKey(nameof(ID), nameof(DateTime))]

Generates only the primary key for both version 8.0.3 and 9.0.0

Note: The order of columns in the composite primary was already reversed in v8.0.3 and seems correct in v9.0.0

However both v.8.0.3 and v9.0.0 should generate the same script for any existing migration.