Multi column PK may result in System.Data.ConstraintException when PK is widened

Description

After extending multi column PK, bulk insert with the data different only in the new column fails with System.Data.ConstraintException.

Repro steps

1. Create table EFTests:

CREATE TABLE [dbo].[EFTests] (
    [ZipCode] [nvarchar](5) NOT NULL,
    [City] [nvarchar](50) NOT NULL,
    [CityOriginalName] [nvarchar](50) NOT NULL,
    CONSTRAINT [PK_dbo.EFTests] PRIMARY KEY ([ZipCode], [City], [CityOriginalName])
)
CREATE INDEX [IX_Test_City] ON [dbo].[EFTests]([City])

2. Modify table:

ALTER TABLE [dbo].[EFTests] DROP CONSTRAINT [PK_dbo.EFTests]
ALTER TABLE [dbo].[EFTests] ADD [StateCode] [nvarchar](2) NOT NULL DEFAULT ''
ALTER TABLE [dbo].[EFTests] ADD CONSTRAINT [PK_dbo.EFTests] PRIMARY KEY ([ZipCode], [StateCode], [City], [CityOriginalName])
CREATE INDEX [IX_Test_StateCode] ON [dbo].[EFTests]([StateCode])

3. Attempt to run the test (adjust type RatingDB for your local environment, RatingSqlProviderName is just a const string). The set up is:

    type RatingDB = SqlProgrammabilityProvider<RatingSqlProviderName, ConfigFile = AppConfigFile>


    type TestTbl = RatingDB.dbo.Tables.EFTests
    type TestTblRow = TestTbl.Row
    type TruncateTestTbl = SqlCommandProvider<"truncate table dbo.EFTests", SwyfftRatingSqlProviderName, ConfigFile = AppConfigFile>


    type ZipCodeCityInfo =
        {
            ZipCode : string
            State : string
            City : string
            CityOriginalName : string
        }

and the test is (adjust inheritance / code to get to SQL connection - use conn = ...):

open System
open Xunit
open Xunit.Abstractions
open System.Data.SqlClient
open FSharp.Data

type DynamicDataTests(output : ITestOutputHelper) =
    inherit IntegrationTestBase(output)

    [<Fact>]
    member _.bulkInsertTest() =
        use ctx = base.DbFactory.Value.GetRatingContext()
        let db = EntityHelpers.GetDatabase ctx
        let connStr = db.Connection.ConnectionString
        let getConn = RatingConnectionGetter (fun () -> new SqlConnection(connStr))
        use conn = getConn.getConnection()

        let data =
            [
                {
                    ZipCode = "12345"
                    State = "MA"
                    City = "ABCDEFGH"
                    CityOriginalName = "ABCDEFGH"
                }

                {
                    ZipCode = "12345"
                    State = "CT"
                    City = "ABCDEFGH"
                    CityOriginalName = "ABCDEFGH"
                }
            ]

        let updateTbl (t : TestTbl) (r : ZipCodeCityInfo) =
            let newRow =
                t.NewRow(
                    City = r.City,
                    StateCode = r.State,
                    ZipCode = r.ZipCode
                    )

            newRow.CityOriginalName <- r.CityOriginalName
            t.Rows.Add newRow

        use truncateTbl = new TruncateTestTbl(conn, commandTimeout = 3600)
        truncateTbl.Execute() |> ignore

        use tbl = new TestTbl()
        data |> List.map (fun e -> updateTbl tbl e) |> ignore
        use bulkCopy = new SqlBulkCopy(conn, BulkCopyTimeout = 3600, DestinationTableName = "EFTests");
        bulkCopy.WriteToServer (tbl) |> ignore

        0

3. Observe that the test fails with something like:

System.Data.ConstraintException
Column 'ZipCode, City, CityOriginalName' is constrained to be unique.  Value '12345, ABCDEFGH, ABCDEFGH' is already present.
   at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action)
   at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
   at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean suppressEnsurePropertyChanged, Int32 position, Boolean fireEvent, Exception& deferredException)
   at System.Data.DataTable.InsertRow(DataRow row, Int64 proposedID, Int32 pos, Boolean fireEvent)
   at <StartupCode$FSharp-Data-SqlClient>.$DataTable.get_Rows@15.System-Collections-Generic-ICollection`1-Add(T row) in C:\dev\src\github.com\fsprojects\FSharp.Data.SqlClient\src\SqlClient\DataTable.fs:line 28
   at <StartupCode$__________-Services-AddressProcessorTests>.$DynamicDataTests.updateTbl@91.Invoke(DataTable`1 t, ZipCodeCityInfo r) in C:\Source\__________\__________.Services.AddressProcessorTests\DynamicDataTests.fs:line 99
   at Microsoft.FSharp.Primitives.Basics.List.mapToFreshConsTail[a,b](FSharpList`1 cons, FSharpFunc`2 f, FSharpList`1 x) in E:\A\_work\130\s\src\fsharp\FSharp.Core\local.fs:line 243
   at Microsoft.FSharp.Primitives.Basics.List.map[T,TResult](FSharpFunc`2 mapping, FSharpList`1 x) in E:\A\_work\130\s\src\fsharp\FSharp.Core\local.fs:line 254
   at __________.Services.AddressProcessorTests.DynamicDataTests.bulkInsertTest() in C:\Source\__________\__________.Services.AddressProcessorTests\DynamicDataTests.fs:line 105

The __________ contain irrelevant proprietary information.

Note that StateCode is missing in the error message.

Expected behavior

Observe that if the table is created in one step:

CREATE TABLE [dbo].[EFTests] (
    [ZipCode] [nvarchar](5) NOT NULL,
    [StateCode] [nvarchar](2) NOT NULL,
    [City] [nvarchar](50) NOT NULL,
    [CityOriginalName] [nvarchar](50) NOT NULL,
    CONSTRAINT [PK_dbo.EFTests] PRIMARY KEY ([ZipCode], [StateCode], [City], [CityOriginalName])
)
CREATE INDEX [IX_Test_StateCode] ON [dbo].[EFTests]([StateCode])
CREATE INDEX [IX_Test_City] ON [dbo].[EFTests]([City])

then the test will pass.

Actual behavior

The test fails.

Known workarounds

Drop the table and recreate it in one step. This is often not an option.

Related information

• Operating system: Windows 10
• Database version: MSSQL 2019
• .NET Runtime: NET Framework 4.7.2
• Editing Tools: VS 2019

Start at src/SqlClient/DataTable.fs line 28, where the stack trace shows the generated DataTable adding rows, and reproduce the schema migration followed by SqlBulkCopy. Inspect how the primary-key uniqueness constraint is built after a composite key is widened. Done means the constraint includes StateCode and the supplied bulk-insert reproduction passes without System.Data.ConstraintException.