CREATE UNIQUE INDEX duplicate key when I update-database by EF Core

.net-core c# ef-migrations entity-framework-core sql

Question

I have troubles with an error when I am making the migration to database

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Cities' and the index name 'IX_Cities_CountryId'. The duplicate key value is (1). The statement has been terminated.

I write next code for context table and columns configuration:

public class CityConfiguration : IEntityTypeConfiguration<City>
    {
        public void Configure(EntityTypeBuilder<City> builder)
        {
            builder.HasKey(x => x.Id).HasName("PK_City_Id");
            builder.Property(x => x.Id).ValueGeneratedOnAdd();

            builder.HasIndex(x => x.Name).IsUnique().HasName("IX_dbo_Cities_Name");
            builder.Property(x => x.Name).HasMaxLength(64)
                                         .IsRequired();

            builder.Property(x => x.Region).HasMaxLength(64)
                                           .IsRequired();

            builder.Property(x => x.CountryId).IsRequired();

            builder.HasOne<Country>(x => x.Country)
                   .WithOne(x => x.City)
                   .HasForeignKey<City>(x => x.CountryId);

            builder.HasData(InitDefaultCities());
        }

        private City[] InitDefaultCities()
        {
            var cities = new City[]
            {
                new City() { Id = 1, Name = "Atlanta", Region = "Georgia", CountryId = 1,   },
                new City() { Id = 2, Name = "Boston", Region = "Massachusetts", CountryId = 1 },
                new City() { Id = 3, Name = "Brooklyn", Region = "New York", CountryId = 1 },
                new City() { Id = 4, Name = "Charlotte", Region = "North Carolina", CountryId = 1 },
                new City() { Id = 5, Name = "Chicago", Region = "Illinois", CountryId = 1 },
                new City() { Id = 6, Name = "Cleveland", Region = "Ohio", CountryId = 1 },
                new City() { Id = 7, Name = "Dallas", Region = "Texas", CountryId = 1 },
                new City() { Id = 8, Name = "Denver", Region = "Colorado", CountryId = 1 },
                new City() { Id = 9, Name = "Detroit", Region = "Michigan", CountryId = 1 },
                new City() { Id = 10, Name = "San Francisco", Region = "California", CountryId = 1 },
                new City() { Id = 11, Name = "Houston", Region = "Texas", CountryId = 1 },
                new City() { Id = 12, Name = "Indianapolis", Region = "Indiana", CountryId = 1 },
                new City() { Id = 13, Name = "Los Angeles", Region = "California", CountryId = 1 },
                new City() { Id = 14, Name = "Memphis", Region = "Tennessee", CountryId = 1 },
                new City() { Id = 15, Name = "Miami", Region = "Florida", CountryId = 1 },
                new City() { Id = 16, Name = "Milwaukee", Region = "Wisconsin", CountryId = 1 },
                new City() { Id = 17, Name = "Minneapolis", Region = "Minnesota", CountryId = 1 },
                new City() { Id = 18, Name = "New Orlean", Region = "‎Louisiana", CountryId = 1 },
                new City() { Id = 19, Name = "Manhattan", Region = "New York", CountryId = 1 },
                new City() { Id = 20, Name = "Oklahoma", Region = "Oklahoma City", CountryId = 1 },
                new City() { Id = 21, Name = "Orlando", Region = "Florida", CountryId = 1 },
                new City() { Id = 22, Name = "Philadelphia", Region = "Pennsylvania", CountryId = 1 },
                new City() { Id = 23, Name = "Phoenix", Region = "Arizona", CountryId = 1 },
                new City() { Id = 24, Name = "Portland", Region = "Oregon", CountryId = 1 },
                new City() { Id = 25, Name = "Sacramento", Region = "California", CountryId = 1 },
                new City() { Id = 26, Name = "San Antonio", Region = "Texas", CountryId = 1 },
                new City() { Id = 27, Name = "Toronto", Region = "Ontario", CountryId = 2 },
                new City() { Id = 28, Name = "Salt Lake City", Region = "Utah", CountryId = 1 },
                new City() { Id = 29, Name = "Washinton D.C.", Region = "Washinton", CountryId = 1 }

            };

            return cities;
        }
    }

After add-migration it transforms to

migrationBuilder.CreateTable(
            name: "Cities",
            schema: "dbo",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(maxLength: 64, nullable: false),
                Region = table.Column<string>(maxLength: 64, nullable: false),
                CountryId = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_City_Id", x => x.Id);
                table.ForeignKey(
                    name: "FK_Cities_Countries_CountryId",
                    column: x => x.CountryId,
                    principalSchema: "dbo",
                    principalTable: "Countries",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

migrationBuilder.InsertData(
            schema: "dbo",
            table: "Cities",
            columns: new[] { "Id", "CountryId", "Name", "Region" },
            values: new object[,]
            {
                { 1, 1, "Atlanta", "Georgia" },
                { 28, 1, "Salt Lake City", "Utah" },
                { 26, 1, "San Antonio", "Texas" },
                { 25, 1, "Sacramento", "California" },
                { 24, 1, "Portland", "Oregon" },
                { 23, 1, "Phoenix", "Arizona" },
                { 22, 1, "Philadelphia", "Pennsylvania" },
                { 21, 1, "Orlando", "Florida" },
                { 20, 1, "Oklahoma", "Oklahoma City" },
                { 19, 1, "Manhattan", "New York" },
                { 18, 1, "New Orlean", "‎Louisiana" },
                { 17, 1, "Minneapolis", "Minnesota" },
                { 16, 1, "Milwaukee", "Wisconsin" },
                { 29, 1, "Washinton D.C.", "Washinton" },
                { 15, 1, "Miami", "Florida" },
                { 13, 1, "Los Angeles", "California" },
                { 12, 1, "Indianapolis", "Indiana" },
                { 11, 1, "Houston", "Texas" },
                { 10, 1, "San Francisco", "California" },
                { 9, 1, "Detroit", "Michigan" },
                { 8, 1, "Denver", "Colorado" },
                { 7, 1, "Dallas", "Texas" },
                { 6, 1, "Cleveland", "Ohio" },
                { 5, 1, "Chicago", "Illinois" },
                { 4, 1, "Charlotte", "North Carolina" },
                { 3, 1, "Brooklyn", "New York" },
                { 2, 1, "Boston", "Massachusetts" },
                { 14, 1, "Memphis", "Tennessee" },
                { 27, 2, "Toronto", "Ontario" }
            });

migrationBuilder.CreateIndex(
            name: "IX_Cities_CountryId",
            schema: "dbo",
            table: "Cities",
            column: "CountryId",
            unique: true);

        migrationBuilder.CreateIndex(
            name: "IX_dbo_Cities_Name",
            schema: "dbo",
            table: "Cities",
            column: "Name",
            unique: true);

I can't find where is trouble, I rewrite it to SQL script by hand. However, not a problem with data, all unique. If I comment the unique index it will work fine. And after that, I will check the data by the script from documentation

    SELECT 
    Name, 
    COUNT(Name)
FROM 
    dbo.Cities
GROUP BY 
    Name
HAVING 
    COUNT(Name) > 1;

it displayed an empty result!

1
1
4/8/2020 9:03:22 AM

Accepted Answer

The unique index is generated by EF Core because you have configured one-to-one relatiosnhip between City and Country:

builder.HasOne<Country>(x => x.Country)
       .WithOne(x => x.City)
       .HasForeignKey<City>(x => x.CountryId);

Apparently this is not what the data demands - this should be one-to-many relationship instead (City has one Country, but Country has many cities).

So replace the City navigation property in Country with something like this

public ICollection<City> Cities { get; set; }

and respectively

builder.HasOne(x => x.Country)
   .WithMany(x => x.Cities)
   .HasForeignKey(x => x.CountryId);

And don't forget to regenerate the migration.

1
4/8/2020 9:16:37 AM

Popular Answer

You're creating a unique index on the column CountryId with this code:

migrationBuilder.CreateIndex(
    name: "IX_Cities_CountryId",
    schema: "dbo",
    table: "Cities",
    column: "CountryId",
    unique: true);

Since that is not unique I'd suggest setting unique to false. Typo maybe?



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow