search

fsprojects / FSharp.Data.SqlClient

A set of F# Type Providers for statically typed access to MS SQL database
http://fsprojects.github.io/FSharp.Data.SqlClient/
Other
205 stars 71 forks source link

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

Open kkkmail opened 4 years ago

kkkmail commented 4 years ago

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
  1. 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

smoothdeveloper commented 3 years ago

@kkkmail thanks for the bug report.

this is due to the default constraint on the column:

https://github.com/fsprojects/FSharp.Data.SqlClient/blob/8dd0898fbe9663a7785f2b094a30bf6f36e477cc/src/SqlClient.DesignTime/SqlClientProvider.fs#L294-L299

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