Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

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

Aperta
  • Qualcosa non va?
#376 2 commenti 0 reazioni 0 assegnatari Vedi su GitHub

Nessuno ha ancora preso questa issue.

  • #462 di @github-actions — chiusa senza merge

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
45/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Ferma
Stack tecnologico
fsharp, sql
Ambito
database

Direzione di ricerca

Inizia da src/SqlClient/DataTable.fs alla riga 28, dove lo stack trace mostra che la DataTable generata aggiunge righe, e riproduci la migrazione dello schema seguita da SqlBulkCopy. Esamina come viene costruito il vincolo di unicità della chiave primaria dopo l’ampliamento di una chiave composta. Il lavoro è completato quando il vincolo include StateCode e la riproduzione fornita dell’inserimento bulk viene eseguita senza System.Data.ConstraintException.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

bug
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])
  1. 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])
  1. 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>[email protected]`1-Add(T row) in C:\dev\src\github.com\fsprojects\FSharp.Data.SqlClient\src\SqlClient\DataTable.fs:line 28
   at <StartupCode$__________-Services-AddressProcessorTests>[email protected](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
Lingua principale
F#
Stelle
206
Fork
66
Nessuna PR unita negli ultimi 30g

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di fsprojects/FSharp.Data.SqlClient

Tutte le issue di fsprojects/FSharp.Data.SqlClient

Issue simili

Altre issue su Databases

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.