SQL Server 2005: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint

Here is my table Structure:

CREATE TABLE [dbo].[Invoice](
    [InvoiceNumber] [int] IDENTITY(1,1) NOT NULL,
    [InvoiceDate] [datetime] NOT NULL,
    [DueDate] [datetime] NOT NULL,
    [SubTotal] [numeric](18, 2) NULL,
    [Tax] [numeric](18, 2) NULL CONSTRAINT [DF_Invoice_Tax]  DEFAULT (0.0),
    [InvoiceTotal] [numeric](18, 2) NOT NULL CONSTRAINT [DF_Invoice_InvoiceTotal]  DEFAULT (0.0),
    [Remark] [text] NULL,
 CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED 
(
    [InvoiceNumber] ASC
)
GO

CREATE TABLE [dbo].[InvoiceDetail](
    [InvoiceDetailID] [int] IDENTITY(1,1) NOT NULL,
    [InvoiceNumber] [int] NULL,
    [Amount] [numeric](18, 2) NOT NULL,
    [Tax] [numeric](18, 2) NOT NULL CONSTRAINT [DF_InvoiceDetail_Tax]  DEFAULT (0.0),
    [TransactionType] [varchar](2) NULL,
    [Remark] [text] NULL,
 CONSTRAINT [PK_InvoiceDetail] PRIMARY KEY CLUSTERED 
(
    [InvoiceDetailID] ASC
)
GO

ALTER TABLE [dbo].[InvoiceDetail]  WITH NOCHECK ADD  CONSTRAINT [FK_InvoiceDetail_Invoice] FOREIGN KEY([InvoiceNumber])
REFERENCES [dbo].[Invoice] ([InvoiceNumber])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Here is my code snippet for inserting fresh invoice and invoice details data:

    try
    {
        using (TransactionScope transactionScope = new TransactionScope())
        {

            //... Lots of other insert / update / delete operations ...

            invoice.Add(); //Adds invoice to DB and sets the PK value in invoice.InvoiceNumber;

            foreach (InvoiceDetail invoiceDetail in invoice.InvoiceDetails.Values)
            {
                invoiceDetail.InvoiceNumber = invoice.InvoiceNumber;
                invoiceDetail.AddInvoiceDetail(); //Randomly fails.
            }

            //... Lots of other code + Payment Gateway integration
            transactionScope.Complete();
        }
    }
    catch (Exception ex)
    {
        errorType = ErrorType.General;
        if (HttpContext.Current != null)
        {
            Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
        }
        else
        {
            Elmah.ErrorLog errorLog = Elmah.ErrorLog.GetDefault(null);
            errorLog.ApplicationName = "Billing Engine";
            errorLog.Log(new Elmah.Error(ex));
        }
    }

I use Enterprise Library for DB operations.

I get random exceptions at line# invoiceDetail.AddInvoiceDetail();

I have advised my staff to re-try whenever failure occurs. Re-try of the same operation without any changes passes. We have hundreds of transactions happening daily via this code. And daily I see one or two transactions failing with the below error.

ELMAH logs the following error:

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_InvoiceDetail_Invoice". The conflict occurred in database "XXXX", table "dbo.Invoice", column 'InvoiceNumber'. The statement has been terminated.

I modified this code to serialize and store invoice + invoicedetails when an exception is caught. Strangely, I was able to see invoicenumber was correctly generated and set in the invoice object. Also, first invoice detail insertion operation succeeded (because serialized data had invoicedetailid set for one invoicedetail but zero for all others). So I assume the second invoicedetail insertion failed with the FK error.

This is totally random, 98% of times it passed and all failures pass upon re-try.

Things that I have tried:

1. I have checked there is no corrupt data in InvoiceDetails table.

2. I have executed sp_updatestats

3. Someone suggested this is a bug in SQL Server 2005 http://ift.tt/1N2pyFg http://ift.tt/1N2pANm

So we got the updates installed on SQL Server 2005. Upto SP4 (Version : 9.00.5057)

But still no good. I keep getting these random failures.

I suspect this is some sort of concurrency issue or may be some transactionscope problem. But I have no clue how to debug further.