SqlBulkCopy giving FOREIGN KEY constraint error

sqlbulkcopy sql-server-2016

Question

I am using SqlBulkCopy to bulk insert records into a database.

Below is the code for it. What bothers me is that when I use SqlBulkCopy I get FOREIGN KEY constraint error and for exact same records if I use UNION ALL approach it works just fine. What am I doing wrong here?

public partial class Repository
{
    public bool InsertResult(List<string> kitIds)
    {
        if (kitIds == null || kitIds.Count == 0)
            return false;

        using (var connection = (SqlConnection)_database.CreateConnection())
        {
            connection.Open();

            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    using (SqlBulkCopy copy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, transaction))
                    {
                        /*This works*/
                        var sb = new StringBuilder(2048);
                        sb.AppendLine("INSERT INTO KITSTATUSES (KitId, StatusId, IsActiveStatus) ");

                        for (int i = 0; i < kitIds.Count; i++)
                        {
                            sb.AppendLine($"SELECT '{kitIds[i]}', {(int)StatusOfKit.ResultUploaded}, 1");
                            sb.AppendLine("UNION ALL ");
                        }

                        sb.Remove(sb.Length - 12, 12);
                        connection.Execute(sb.ToString(), null, transaction);

                        /*DOES NOT WORK and throws error:
                         * The INSERT statement conflicted with the FOREIGN KEY constraint "FK_KitStatuses_Kits". The conflict occurred in database "GeneBlueprint", table "dbo.Kits", column 'KitId'.
                         * The statement has been terminated.
                        var kitStatuses = kitIds.Select(k => new KitStatus { KitId = k, IsActiveStatus = true, StatusId = (int)StatusOfKit.ResultUploaded }).ToList();
                        using (var reader = ObjectReader.Create(kitStatuses, "KitId", "StatusId", "IsActiveStatus"))
                        {
                            //Verify that reader has right values
                            //while (reader.Read())
                            //{
                            //    Debug.WriteLine($"KitId: {reader.GetFieldValue<string>(0)}, StatusId: {reader.GetFieldValue<int>(1)}, IActiveStatus: {reader.GetFieldValue<bool>(2)}, StatusDate: {reader.GetFieldValue<DateTime>(3)}");
                            //}
                            copy.DestinationTableName = "KitStatuses";
                            copy.WriteToServer(reader);
                        }
                        */

                        /*DOES NOT WORK and throws error
                         * The INSERT statement conflicted with the FOREIGN KEY constraint "FK_KitStatuses_Kits". The conflict occurred in database "GeneBlueprint", table "dbo.Kits", column 'KitId'.
                         * The statement has been terminated.
                        DataTable dt = new DataTable();
                        dt.Columns.Add(new DataColumn() { ColumnName = "KitId", DataType = typeof(string), MaxLength = 15, AllowDBNull = false, AutoIncrement = false });
                        dt.Columns.Add(new DataColumn() { ColumnName = "StatusId", DataType = typeof(int), AllowDBNull = false, AutoIncrement = false, DefaultValue = 8 });
                        dt.Columns.Add(new DataColumn() { ColumnName = "IsActiveStatus", DataType = typeof(bool), AllowDBNull = false, AutoIncrement = false, DefaultValue = true });
                        for (int i = 0; i < kitIds.Count; i++)
                        {
                            var row = dt.NewRow();
                            row[0] = kitIds[i];
                            row[1] = 8;
                            row[2] = true;
                            dt.Rows.Add(row);
                        }

                        using (var reader = ObjectReader.Create(kitStatuses, "KitId", "StatusId", "IsActiveStatus", "StatusDate"))
                        {
                            copy.DestinationTableName = "KitStatuses";
                            copy.WriteToServer(dt);
                        }
                        */
                    }

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        return true;
    }
}

Below is script for tables relevant to this question:

CREATE TABLE [dbo].[Kits] 
(
    [KitId] [nvarchar](15) NOT NULL,
    CONSTRAINT [PK_Kits] PRIMARY KEY CLUSTERED ([KitId] ASC)
               WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                     IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                     ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[KitStatuses]
(
    [KitStatusId] [int] IDENTITY(1,1) NOT NULL,
    [KitId] [nvarchar](15) NOT NULL,
    [StatusId] [int] NOT NULL,
    [StatusDate] [datetime] NOT NULL,
    [IsActiveStatus] [bit] NOT NULL,

    CONSTRAINT [PK_KitStatuses] PRIMARY KEY CLUSTERED([KitStatusId] ASC)
               WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                     IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                     ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT [IX_KitStatuses_KitId_KitStatus] 
       UNIQUE NONCLUSTERED ([KitId] ASC, [KitStatusId] ASC)
               WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                     IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                     ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Statuses]
(
    [StatusId] [int] IDENTITY(1,1) NOT NULL,
    [StatusName] [nvarchar](50) NOT NULL,

    CONSTRAINT [PK_Statuses] PRIMARY KEY CLUSTERED ([StatusId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[KitStatuses] 
  ADD CONSTRAINT [DF_KitStatuses_StatusDate] 
      DEFAULT (getdate()) FOR [StatusDate]

ALTER TABLE [dbo].[KitStatuses] 
  ADD CONSTRAINT [DF_KitStatuses_IsActiveStatus]  
      DEFAULT ((1)) FOR [IsActiveStatus]

ALTER TABLE [dbo].[KitStatuses] WITH CHECK 
  ADD CONSTRAINT [FK_KitStatuses_Kits] 
      FOREIGN KEY([KitId]) REFERENCES [dbo].[Kits] ([KitId])

ALTER TABLE [dbo].[KitStatuses] CHECK CONSTRAINT [FK_KitStatuses_Kits]

ALTER TABLE [dbo].[KitStatuses] WITH CHECK 
  ADD CONSTRAINT [FK_KitStatuses_Statuses] 
      FOREIGN KEY([StatusId]) REFERENCES [dbo].[Statuses] ([StatusId])

ALTER TABLE [dbo].[KitStatuses] CHECK CONSTRAINT [FK_KitStatuses_Statuses]

I am using below technologies:

  • Enterprise Library's Data Access block for DB Connections.
  • Fast member to convert List to DataReader

Accepted Answer

The issue is caused because SqlBulkCopy insert:

  • The KitId in the StatusId column
  • The StatusId in the KitId column

AutoMapping is not really smart...

Firstly, all columns are mapped by ordinal:

  • DataColumn 0 (KitId) mapped to TableColumn 0 (KitStatusId)
  • DataColumn 1 (StatusId) mapped to TableColumn1 (KitIt)
  • DataColumn 2 (IsActiveStatus) mapped to TableColumn2 (StatusId)

SqlBulkCopy Code

internal void CreateDefaultMapping(int columnCount)
{
  for (int index = 0; index < columnCount; ++index)
    this.InnerList.Add((object) new SqlBulkCopyColumnMapping(index, index));
}

Secondly, since the KitStatusId is an identity, the AutoMapping will try to map to the next available column, and since IsActiveStatus doesn't match with the type of StatusId, the AutoMapping will map the KitId to the StatusId column.

If you look at the SQL generated, you will see the IsActiveStatus is not mapped

insert bulk KitStatuses ([KitId] NVarChar(15) COLLATE SQL_Latin1_General_CP1_CI_AS, [StatusId] Int) with (CHECK_CONSTRAINTS)

In short, never trust the AutoMapping this will only lead to some error.

Map your column explicitly instead

copy.ColumnMappings.Add("KitId", "KitId");
copy.ColumnMappings.Add("StatusId", "StatusId");
copy.ColumnMappings.Add("IsActiveStatus", "IsActiveStatus");


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why