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

[kkkmail]

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

[smoothdeveloper]

@kkkmail thanks for the bug report.

this is due to the default constraint on the column:

FSharp.Data.SqlClient/src/SqlClient.DesignTime/SqlClientProvider.fs

Lines 294 to 299 in 8dd0898

	if c.DefaultConstraint <> "" && c.PartOfUniqueKey
	then
	{ c with PartOfUniqueKey = false }
	//ADO.NET doesn't allow nullable columns as part of primary key
	//remove from PK if default value provided by DB on insert.
	else c

According to the comment, there is a backing for this behaviour being the way it is.

If you alter the constraint to not have a default value:

ALTER TABLE [dbo].[EFTests] ADD [StateCode] [nvarchar](2) not null

Then it will work as expected.

I'd like to consider that default value on column that is part of primary key is double design smell, you can't update a primary key column and it is reasonable to expect insert statements to specify the value.

If you want to insert a default value just for the time of creating the new column, you should drop the default constraint and specify the value being not null.

Please let me know if you agree to close this by design and if the work around is good enough?

code to reproduce in a script:

#r "System.Data"
#r "nuget: FSharp.Data.SqlClient"

[<Literal>]
let connectionString = "Data Source=.;Initial Catalog=testtp;Integrated Security=True;"
open FSharp.Data
open System.Data.SqlClient

type RatingDB = SqlProgrammabilityProvider<connectionString>

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

type ZipCodeCityInfo =
    {
        ZipCode : string
        State : string
        City : string
        CityOriginalName : string
    }
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
let conn = connectionString
do
  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