INSERT Statement conflicted with Foreign Key constraint

Question

Project background: working with MS-Access 2010 user front end and SQL Server 2008 back end that stores the database. I write stored procedures in SQL and run them on the SQL Server then call them in VBA from Access.

When a button is pressed after retrieving the values needed and storing them to the appropriate variable, I call my stored procedure and pass it the needed parameters. The desired effect is for those values to be stored in the table tblFromICPMS. But when I press the button I get the error:

-2147217873
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblFromICPMS_tblWOSampleTest". The conflict occurred in database "GSATest", table "dbo.tblWOSampleTest". in ImportICPMS

I have looked at people questions with a similar issue and most of the time the problem seems to lie in their stored procedure. I can't really figure out what is wrong with my stored procedure and what the conflict could be caused by.

Provided spec: In a spec I was given, I was provided with SQL code to make a table (TblFromICPMS) with foreign keys.

Here is the script for that:

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].    [FK_tblFromICPMS_tblWOSampleTest]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFromICPMS]'))
    ALTER TABLE [dbo].[tblFromICPMS] 
      DROP CONSTRAINT [FK_tblFromICPMS_tblWOSampleTest]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_tblFromICPMS_DateImported]') AND type = 'D')
BEGIN
   ALTER TABLE [dbo].[tblFromICPMS] 
     DROP CONSTRAINT [DF_tblFromICPMS_DateImported]
END
GO

/****** Object:  Table [dbo].[tblFromICPMS]    Script Date: 5/14/2014 9:29:33 AM ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblFromICPMS]') AND type in (N'U'))
   DROP TABLE [dbo].[tblFromICPMS]
GO

/****** Object:  Table [dbo].[tblFromICPMS]    Script Date: 5/14/2014 9:29:33 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblFromICPMS]') AND type in (N'U'))
BEGIN
   CREATE TABLE [dbo].[tblFromICPMS]
   (
[FromICPMSID] [int] IDENTITY(1,1) NOT NULL,
[Sample Name] [nvarchar](255) NULL,
[Date And Time Acquired] [nvarchar](255) NULL,
[Analyte] [nvarchar](20) NULL,
[Concentration] [nvarchar](255) NULL,
[Units] [nvarchar](255) NULL,
[DateImported] [datetime] NOT NULL,
[DateAnalyzed] [datetime] NULL,
[BatchID] [int] NOT NULL,
[QueueID] [int] NULL,
[WOID] [nvarchar](10) NULL,
[SampleID] [int] NULL,
[TestID] [int] NULL,
[ActResult] [float] NULL,
[Issue] [nvarchar](255) NULL,
CONSTRAINT [PK_tblFromICPMS] PRIMARY KEY CLUSTERED ([FromICPMSID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,   ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].   [DF_tblFromICPMS_DateImported]') AND type = 'D')
BEGIN
    ALTER TABLE [dbo].[tblFromICPMS] 
    ADD CONSTRAINT [DF_tblFromICPMS_DateImported] 
        DEFAULT (getdate()) FOR [DateImported]
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].   [FK_tblFromICPMS_tblWOSampleTest]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFromICPMS]'))
   ALTER TABLE [dbo].[tblFromICPMS] WITH CHECK 
   ADD CONSTRAINT [FK_tblFromICPMS_tblWOSampleTest] 
       FOREIGN KEY([WOID], [SampleID], [TestID])
       REFERENCES [dbo].[tblWOSampleTest] ([WOID], [SampleID], [TestID])
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblFromICPMS_tblWOSampleTest]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblFromICPMS]'))
   ALTER TABLE [dbo].[tblFromICPMS] CHECK CONSTRAINT [FK_tblFromICPMS_tblWOSampleTest]
GO

And here is the script for my stored procedure:

CREATE PROCEDURE upInsertFromICPMSRecord 
    @SampleName nvarchar(60), 
    @DateTimeAcquired nvarchar(60), 
    @Analyte nvarchar(60), 
    @Concentration nvarchar(60), 
    @QueueID nvarchar(60), 
    @Units nvarchar(60), 
    @ThisWOID nvarchar(60),
    @ThisSampleID nvarchar(60), 
    @ThisTestID nvarchar(60),
    @ActResult nvarchar(60), 
    @Issue nvarchar(60), 
    @BatchID nvarchar(60)   
AS 
   INSERT INTO tblFromICPMS ("Sample Name","Date And TimeAcquired",Analyte,concentration,Units,DateImported,DateAnalyzed,BatchID, QueueID, WOID, SampleID, TestID, ActResult, Issue)
   VALUES (@SampleName, @DateTimeAcquired, @Analyte, @Concentration,
           @Units, getdate(), 
           CONVERT(datetime, @DateTimeAcquired), @BatchID, @QueueID, 
           @ThisWOID, @ThisSampleID, @ThisTestID, @ActResult, @Issue);
GO

For some additional background, another stored procedure I was required to make was GetTestIdForAnalyte which basically tested to see if a TESTID exists for the particular file being processed. It has an output parameter that stores the number of rows affected. Therefore if it is greater than zero I know a TestID exists. I am working on a test database and the files I am working on are not in the system so the testID is never going to exist. I don't know if this is causing the issue, but here is the stored procedure for that.

CREATE PROCEDURE upGetTestIDForAnalyte 
    @WOID nvarchar(60), 
    @SampleID nvarchar(60),
    @Analyte nvarchar(60), 
    @RecordsAffected int OUT
AS
   SELECT 
       TestID = t1.TestID
   FROM 
       tblWOSampleTest t1
   JOIN 
       tblTest t2 ON t1.TestID = t2.TestID; 
   WHERE 
       @WOID = t1.WOID 
       AND @SampleID = t1.SampleID 
       AND @Analyte = t2.Analyte

   SET @RecordsAffected = @@ROWCOUNT
GO  

If there is a TestID then I can continue on and insert the records by calling my other stored procedure, else I insert an error message into an error log. I just kind of ignored that for the sake of testing because I thought It wouldn't matter.

Answer 1

This is basically just the constraint telling you that you're trying to insert a record into tblFromICPMS that does not have a matching record in tblWOSampleTest with the same WOID, SampleID, and TestID.

With the constraint in place, you MUST have a record in tblWOSampleTest with those values BEFORE you can insert them into tblFromICPMS - thats what a foesign key constraint is..

http://www.w3schools.com/sql/sql_foreignkey.asp