Stored procedure foreign key constraint

Question

I don't know if I am using the correct syntax but I am attempting to enter into 1 table, and then another table which has a foreign key. The primary key from tbl1SentEmails is used as a foreign key into tbl1Recipients. I've put this in a stored procedure however, I keep getting an error stating foreign key constraint. Is there a way to fix this?

Stored Procedure code:

-- This is the sample script to test the SP
-- An instance of the Table parameter type is created
Declare @SampelData As [dbo].[SampleDataType]
Declare @RecData As [dbo].[SampleRecType]


Insert Into @SampelData(strEmailSendID, dtmSent, strSubject, strBody, strConversationID, strConversationTopic, strConversationIndex, dtmReplied, dtmOpened, dtmClicked, blnTrackOpens, blnTrackClicks, blnTrackReplies, lngMergeID, blnHide)
Values ('ce4d29160fae4eba92c7d233723f6f59', '2016-11-08', 'strSubject', 'strBody','strConversationID', 'strConversationTopic', 'strConversationIndex', '2016-11-08', '2016-11-08', '2016-11-08', 'true', 'true', 'true', 69164, 'true')
SELECT * FROM @SampelData
Insert Into RecData(strEmailSendID, strEmailAddress, strDisplayName, strRecipientType) 
Values ('ce4d29160fae4eba92c7d233723f6f59','dutta@standss.com.fj','Alvindra Dutt','TO')
Select * From @RecData
-- we then call the SP to store the values
Exec SampleProcedure @SampelData,@RecData

I'll be invoking this stored procedure from an MVC application, so I'm just passing in the raw values for the PK for tbl1SentEmails. However, is there anyway I can get the strEmailSendID of the table passed in and then enter that into tbl1Recipients?

This is the create for the Table Types:

-- Create a table data type
CREATE TYPE [dbo].[SampleDataType] As Table
(
--This type has structure similar to the DB table 
--SampleString Nvarchar(64) Not Null -- Having one String
--, SampleDate Date -- and one Date
strEmailSendID nvarchar(255) not null, 
dtmSent datetime not null,
strSubject nvarchar(255) null, 
strBody nvarchar(255) null, 
strConversationID nvarchar(255) null, 
strConversationTopic nvarchar(1000) null, 
strConversationIndex nvarchar(max) null, 
dtmReplied datetime null, 
dtmOpened datetime null, 
dtmClicked datetime null, 
blnTrackOpens bit null, 
blnTrackClicks bit null, 
blnTrackReplies bit null, 
lngMergeID bigint not null, 
blnHide bit null, 
lngLatestEventID bigint null, 
strClickRespone nvarchar(50) null, 
dtmClickResponse datetime null
)


CREATE TYPE [dbo].[SampleRecType] As Table
(
strEmailSendID nvarchar(255) not null, 
strEmailAddress nvarchar(255) not null, 
strDisplayName nvarchar(255) null, 
strRecipientType nvarchar(5) not null
)

Code for the actual stored procedure:

--This is the Stored Procedure
CREATE PROCEDURE [dbo].[SampleProcedure]
(
-- which accepts one table value parameter. 
-- It should be noted that the parameter is readonly
@Sample As [dbo].[SampleDataType] Readonly, @Rec As [dbo].[SampleRecType]    ReadOnly
)
AS

Begin
-- We simply insert values into the DB table from the parameter
-- The table value parameter can be used like a table with only read rights

--Insert Into SampleTable(SampleString,SampleDate)
--Select SampleString,SampleDate From @Sample
Insert Into tbl1Recipients(strEmailSendID, strEmailAddress, strDisplayName, strRecipientType)
Select * From @Rec
Insert Into tbl1SentEmails(strEmailSendID, dtmSent, strSubject, strBody, strConversationID, strConversationTopic, strConversationIndex, dtmReplied, dtmOpened, dtmClicked, blnTrackOpens, blnTrackClicks, blnTrackReplies, lngMergeID, blnHide, lngLatestEventID, strClickResponse, dtmClickResponse)
Select * From @Sample 
End

Code for the tables:

USE [standss_tracking_us_sandbox]
GO

/****** Object:  Table [dbo].[tbl1Recipients]    Script Date: 10/11/2016    5:24:52 p.m. ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl1Recipients](
[strEmailSendID] [nvarchar](255) NOT NULL,
[lngRecipientNo] [bigint] IDENTITY(1,1) NOT NULL,
[strEmailAddress] [nvarchar](255) NOT NULL,
[strDisplayName] [nvarchar](255) NULL,
[strRecipientType] [nvarchar](5) NOT NULL,
[ts] [timestamp] NOT NULL,
 CONSTRAINT [PK_tbl1Recipients] PRIMARY KEY CLUSTERED 
(
[strEmailSendID] ASC,
[lngRecipientNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[tbl1Recipients]  WITH CHECK ADD  CONSTRAINT     [FK_tbl1Recipients_tbl1SentEmails] FOREIGN KEY([strEmailSendID])
REFERENCES [dbo].[tbl1SentEmails] ([strEmailSendID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tbl1Recipients] CHECK CONSTRAINT    [FK_tbl1Recipients_tbl1SentEmails]
GO


/****** Object:  Table [dbo].[tbl1SentEmails]    Script Date: 10/11/2016     5:28:42 p.m. ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl1SentEmails](
[strEmailSendID] [nvarchar](255) NOT NULL,
[dtmSent] [datetime] NOT NULL,
[strSubject] [nvarchar](255) NULL,
[strBody] [nvarchar](255) NULL,
[strConversationID] [nvarchar](255) NULL,
[strConversationTopic] [nvarchar](1000) NULL,
[strConversationIndex] [nvarchar](max) NULL,
[dtmReplied] [datetime] NULL,
[dtmOpened] [datetime] NULL,
[dtmClicked] [datetime] NULL,
[blnTrackOpens] [bit] NULL,
[blnTrackClicks] [bit] NULL,
[blnTrackReplies] [bit] NULL,
[ts] [timestamp] NOT NULL,
[lngMergeID] [bigint] NOT NULL,
[blnHide] [bit] NULL,
[lngLatestEventID] [bigint] NULL,
[strClickResponse] [nvarchar](50) NULL,
[dtmClickResponse] [datetime] NULL,
CONSTRAINT [PK_tbl1SentEmails] PRIMARY KEY CLUSTERED 
(
[strEmailSendID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[tbl1SentEmails]  WITH CHECK ADD  CONSTRAINT    [FK_tbl1SentEmails_tbl1Merges] FOREIGN KEY([lngMergeID])
REFERENCES [dbo].[tbl1Merges] ([lngMergeID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tbl1SentEmails] CHECK CONSTRAINT     [FK_tbl1SentEmails_tbl1Merges]
GO

show us the create tables? and not sure where you get the error. — Juan Carlos Oropeza, Nov 10 '16 at 04:04
@JuanCarlosOropeza, I get the error on the second `INSERT` statement when I want to insert into the `tbl1Recipients`. There is one field called `strEmailSendID` which is the PK for `tbl1SentEmails`, and acts as a FK into `tbl1Recipients` — Rahul Kishore, Nov 10 '16 at 04:09
The statement you provide includes only inserts into table variables, not into `tbl1Recipients` or `tbl1SentEmails` — mendosi, Nov 10 '16 at 04:13
So the inserts into `tbl1Recipients` and `tbl1SentEmails` happen inside the store procedure? Could you provide the code for that? — mendosi, Nov 10 '16 at 04:16
@JuanCarlosOropeza it looks like the stored procedure takes table-valued parameters — mendosi, Nov 10 '16 at 04:17
@mendosi, I've updated everything I got for the stored proc and the table-valued parameters. — Rahul Kishore, Nov 10 '16 at 04:19
Now we need the create table for tblReipients and tblSentEmail because there is where the constrain are — Juan Carlos Oropeza, Nov 10 '16 at 04:19
@JuanCarlosOropeza, I created the table(s) using the designer I don't have the code for that. — Rahul Kishore, Nov 10 '16 at 04:20
You go to the sql managment studio right click the table and select create script — Juan Carlos Oropeza, Nov 10 '16 at 04:21

score 1 · Answer 1 · edited May 23 '17 at 10:30

1

It looks like the stored procedure is inserting into tbl1Recipients before inserting into tbl1SentEmails. SQL Server does not support deferred foreign-key constraints so you need to add the row to the parent table before you add it in the table which references it.

Try switching the INSERT statements in your stored procedure.

edited May 23 '17 at 10:30

Community

1
1

answered Nov 10 '16 at 04:26

mendosi

2,001
1
12
18

score 1 · Accepted Answer · answered Nov 10 '16 at 06:33

Alter your stored procedure as below. It will work for you

ALTER PROCEDURE [dbo].[SampleProcedure]
(
   @Sample As [dbo].[SampleDataType] Readonly,
   @Rec As [dbo].[SampleRecType]    ReadOnly
)
AS
Begin

    Insert Into tbl1SentEmails(strEmailSendID, dtmSent, strSubject, strBody, strConversationID, strConversationTopic, strConversationIndex, dtmReplied, dtmOpened, dtmClicked, blnTrackOpens, blnTrackClicks, blnTrackReplies, lngMergeID, blnHide, lngLatestEventID, strClickResponse, dtmClickResponse)
    Select * From @Sample 

    Insert Into tbl1Recipients(strEmailSendID, strEmailAddress, strDisplayName, strRecipientType)
    Select * From @Rec

End

I just dropped the procedure from SSMS and then re-created with this. Thanks Mansi! — Rahul Kishore, Nov 10 '16 at 19:43

Stored procedure foreign key constraint

2 Answers2