Hi
When I excute my proc I get an error
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ContactInformation_EndUser_iContactInformationID". The conflict occurred in database "MSC-UFCFUD-60-M", table "dbo.Tbl_ContactInformation", column 'ipknContactInformationID'.
My understanding is that if there is no data in the parent table, I will not be able to insert data in to child table. However, my parent table has a row of data, it is fetched by the code, but when the insert statement is executed I get the above error.
My code is below. EndUser Table has three foreign keys. Execution first two insert statement works fine. but the third insert statement fails when Inserting into Tbl_EndUser. The value returned for @ifknEndUserTypeID =1 which is a valid value. I have put the code in bold that fails.
USE [MSC-UFCFUD-60-M]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('sp_EndUser_Insert'))
BEGIN
DROP PROCEDURE [dbo].[sp_EndUser_Insert]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_EndUser_Insert]
@zEndUserType NVARCHAR(50)
,@zFirstName NVARCHAR(50)
,@zLastName NVARCHAR(50)
,@zPhone NVARCHAR(30)
,@zMobile NVARCHAR(30)
,@zFax NVARCHAR(30)
,@zTitle NVARCHAR(10)
,@zGender NVARCHAR(1)
,@dtDOB DATE
,@iAge INT
,@iAddressID INT
,@iContactInformationID INT
,@zPassword NVARCHAR(20)
,@zEMail NVARCHAR(100)
,@zEndUserImage NVARCHAR(100)
,@uidActivationCode UNIQUEIDENTIFIER
,@bUserActivated BIT
,@dteAccountCreationDate DATETIME
,@dteLastLoginDate DATETIME
,@zAddressLine1 NVARCHAR(50)
,@zAddressLine2 NVARCHAR(50)
,@zCity NVARCHAR(50)
,@zPostCode NVARCHAR(10)
AS
BEGIN
-- 1. INSERT THE ADDRESS
BEGIN TRANSACTION
DECLARE @ipAddressID INT
DECLARE @ipContactInformationID INT
DECLARE @ifknEndUserTypeID INT
INSERT INTO Tbl_Address (
zAddressLine1
,zAddressLine2
,zCity
,zPostCode
)
VALUES (
@zAddressLine1
,@zAddressLine2
,@zCity
,@zPostCode
)
SET @ipAddressID = @@IDENTITY
-- TRANSACTION NEEDS TO BE ROLLED BACK IN CASE OF ERROES
IF @@ERROR <> 0
BEGIN
ROLLBACK
-- RAISE AN ERROR AN RETURN
RAISERROR ('Error INSERT INTO Tbl_Address.', 16, 1)
RETURN
END
-- INSERT INFORMATION INTO Tbl_ContactInformation
INSERT INTO Tbl_ContactInformation
(
zPhone
,zMobile
,zFax
,zEMail
)
VALUES
(
@zPhone
,@zMobile
,@zFax
,@zEMail
)
SET @ipContactInformationID = @@IDENTITY
/*
TRANSACTION NEEDS TO BE ROLLED BACK IN CASE OF ERROES
*/
IF @@ERROR <> 0
BEGIN
ROLLBACK
-- RAISE AN ERROR AND RETURN
RAISERROR ('Error INSERT INTO Tbl_ContactInformation.', 16, 1)
RETURN
END
/*
Retrieve EndUserTypeID from the EndUserType table for the selected endusertype
*/
SELECT
@ifknEndUserTypeID = ipknEndUserTypeID
FROM
Tbl_EndUserType
WHERE
zEndUserType = @zEndUserType
/*
INSERT INFORMATION INTO Tbl_EndUser
*/
INSERT INTO Tbl_EndUser
(
ifknEndUserTypeID
,zTitle
,zFirstName
,zLastName
,zGender
,dtDOB
,iAge
,iAddressID
,iContactInformationID
,zPassword
,zEMail
,zEndUserImage
,uidActivationCode
,bUserActivated
,dteAccountCreationDate
)
VALUES
(
@ifknEndUserTypeID
,@zTitle
,@zFirstName
,@zLastName
,@zGender
,@dtDOB
,@iAge
,@iAddressID
,@iContactInformationID
,@zPassword
,@zEMail
,@zEndUserImage
,@uidActivationCode
,0
,getdate()
)
END
