CODEWITHSUNDEEP
sqlsql-serverstored-procedurescursorconstraints
Desmond Chau
Desmond Chau

Reputation: 13

The INSERT statement conflicted with the FOREIGN KEY constraint (Cursor)

My task is to duplicate company information using the stored procedure. I have to use cursor to complete my task as an order from my tech lead. I got this error whenever I run the SP. Other tables such as person, address I have mentioned in my previous questions were solved, now what's left is only phone & phone link table which is giving me headache.

P/S:

Below is my code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DuplicateCompanyInfo]
    @Comp_CompanyId NVARCHAR(80)


AS
BEGIN
    SET NOCOUNT ON;

DECLARE @CompanyID NVARCHAR(30),
        @PersonID NVARCHAR(30),
        @AddressID NVARCHAR(30),
        @PhoneLinkID NVARCHAR(30),
        @PhoneID NVARCHAR(30)
            EXEC @companyId = crm_next_id 5
            EXEC @PersonId = crm_next_id 13
            EXEC @AddressId = crm_next_id 1



        -- Add Company
        INSERT INTO Company
        (
            Comp_CompanyId, Comp_PrimaryPersonId, Comp_PrimaryAddressId, Comp_Name, Comp_Type, Comp_Status, Comp_CreatedBy,
            Comp_CreatedDate, Comp_UpdatedBy, Comp_UpdatedDate, Comp_TimeStamp, Comp_SecTerr, Comp_WebSite
        )
        SELECT  @companyId, @PersonId, @AddressId, Comp_Name, Comp_Type, Comp_Status, '1',
                GETDATE(), '1', GETDATE(), GETDATE(), Comp_SecTerr, Comp_WebSite

        FROM Company
        WHERE Comp_CompanyId = @comp_companyid
        AND Comp_Deleted is null


------- Company PhoneLink (Business) Cursor -----------------------------------------
        -- Declare Variables
        DECLARE @c_PLink_LinkID NVARCHAR(30)
        DECLARE @c_PLink_PhoneId NVARCHAR(30)
        DECLARE @c_PLink_CreatedBy NVARCHAR(30)
        DECLARE @c_PLink_CreatedDate NVARCHAR(30)
        DECLARE @c_PLink_UpdatedDate NVARCHAR(30)
        DECLARE @c_PLink_TimeStamp NVARCHAR(30)
        DECLARE @c_PLink_EntityID NVARCHAR(30)
        DECLARE @c_PLink_RecordID NVARCHAR(30)
        DECLARE @c_PLink_Type NVARCHAR(30)


        --Declare Cursor
        DECLARE CompPhoneLinkCursor CURSOR FOR
        SELECT PLink_LinkID, PLink_PhoneId, PLink_CreatedBy, PLink_CreatedDate, PLink_UpdatedDate, PLink_TimeStamp,
               PLink_EntityID, PLink_RecordID, PLink_Type
        FROM PhoneLink
        INNER JOIN Phone
        ON PLink_PhoneId = Phon_PhoneId
        AND PLink_RecordID = @Comp_CompanyId
        AND Plink_EntityID = '5'
        AND Plink_Type = 'Business'
        AND Phon_Deleted is null
        AND Plink_Deleted is null


        --Open Cursor & fetch 1st row into variables
        OPEN CompPhoneLinkCursor
        FETCH NEXT FROM CompPhoneLinkCursor INTO @c_PLink_LinkID, @c_PLink_PhoneId, @c_PLink_CreatedBy, @c_PLink_CreatedDate,
                                      @c_PLink_UpdatedDate, @c_PLink_TimeStamp, @c_PLink_EntityID, @c_PLink_RecordID, @c_PLink_Type


        --Fetch successful
        --Check for a new row
        WHILE @@FETCH_STATUS = 0
        BEGIN

        EXEC @PhoneLinkID = crm_next_id 10208
        EXEC @PhoneId = crm_next_id 14


            INSERT INTO PhoneLink
            (
                PLink_LinkID, PLink_PhoneId, PLink_CreatedBy, PLink_CreatedDate, PLink_UpdatedDate,
                PLink_TimeStamp, PLink_EntityID, PLink_RecordID, PLink_Type
            )
            VALUES
            (
                @PhoneLinkID, @PhoneId, '1', GETDATE(), GETDATE(),
                GETDATE(), @c_PLink_EntityID, @c_PLink_RecordID, 'Business'
            )


        --Get next available row into variables
        FETCH NEXT FROM CompPhoneLinkCursor INTO @c_PLink_LinkID, @c_PLink_PhoneID, @c_PLink_CreatedBy, @c_PLink_CreatedDate,
                                      @c_PLink_UpdatedDate, @c_PLink_TimeStamp, @c_PLink_EntityID, @c_PLink_RecordID, @c_PLink_Type

        END

        CLOSE CompPhoneLinkCursor
        DEALLOCATE CompPhoneLinkCursor



------- Company Phone (Business) Cursor ---------------------------------------------
        -- Declare Variables
        DECLARE @c_Phon_PhoneId NVARCHAR(30)
        DECLARE @c_Phon_Number NVARCHAR(30)
        DECLARE @c_Phon_CreatedBy NVARCHAR(30)
        DECLARE @c_Phon_CreatedDate NVARCHAR(30)
        DECLARE @c_Phon_UpdatedBy NVARCHAR(30)
        DECLARE @c_Phon_UpdatedDate NVARCHAR(30)
        DECLARE @c_Phon_TimeStamp NVARCHAR(30)


        --Declare Cursor
        DECLARE CompPhoneCursor CURSOR FOR
        SELECT Phon_PhoneId, Phon_Number, Phon_CreatedBy, Phon_CreatedDate, Phon_UpdatedBy, Phon_UpdatedDate, Phon_TimeStamp
        FROM Phone
        INNER JOIN PhoneLink
        ON Phon_PhoneId = PLink_PhoneId
        AND PLink_RecordID = @Comp_CompanyId
        AND Plink_EntityID = '5'
        AND Plink_Type = 'Business'
        AND Phon_Deleted is null
        AND Plink_Deleted is null


        --Open Cursor & fetch 1st row into variables
        OPEN CompPhoneCursor
        FETCH NEXT FROM CompPhoneCursor INTO @c_Phon_PhoneId, @c_Phon_Number, @c_Phon_CreatedBy, @c_Phon_CreatedDate,
                                      @c_Phon_UpdatedBy, @c_Phon_UpdatedDate, @c_Phon_TimeStamp


        --Fetch successful
        --Check for a new row
        WHILE @@FETCH_STATUS = 0
        BEGIN


        INSERT INTO Phone
        (
            Phon_PhoneId, Phon_Number, Phon_CreatedBy, Phon_CreatedDate, Phon_UpdatedBy, Phon_UpdatedDate, Phon_TimeStamp
        )
        VALUES
        (
            @PhoneID, @c_Phon_Number, '1', GETDATE(), '1', GETDATE(), GETDATE()
        )


        --Get next available row into variables
        FETCH NEXT FROM CompPhoneCursor INTO @c_Phon_PhoneId, @c_Phon_Number, @c_Phon_CreatedBy, @c_Phon_CreatedDate,
                                      @c_Phon_UpdatedBy, @c_Phon_UpdatedDate, @c_Phon_TimeStamp

        END

        CLOSE CompPhoneCursor
        DEALLOCATE CompPhoneCursor



------- Company PhoneLink (Fax) Cursor ----------------------------------------------
        -- Declare Variables
        DECLARE @cf_PLink_LinkID NVARCHAR(30)
        DECLARE @cf_PLink_PhoneId NVARCHAR(30)
        DECLARE @cf_PLink_CreatedBy NVARCHAR(30)
        DECLARE @cf_PLink_CreatedDate NVARCHAR(30)
        DECLARE @cf_PLink_UpdatedDate NVARCHAR(30)
        DECLARE @cf_PLink_TimeStamp NVARCHAR(30)
        DECLARE @cf_PLink_EntityID NVARCHAR(30)
        DECLARE @cf_PLink_RecordID NVARCHAR(30)
        DECLARE @cf_PLink_Type NVARCHAR(30)


        --Declare Cursor
        DECLARE CompanyFaxLinkCursor CURSOR FOR
        SELECT PLink_LinkID, PLink_PhoneId, PLink_CreatedBy, PLink_CreatedDate, PLink_UpdatedDate, PLink_TimeStamp,
               PLink_EntityID, PLink_RecordID, PLink_Type
        FROM PhoneLink
        INNER JOIN Phone
        ON PLink_PhoneId = Phon_PhoneId
        AND PLink_RecordID = @Comp_CompanyId
        AND Plink_EntityID = '5'
        AND Plink_Type = 'Fax'
        AND Plink_Deleted is null
        AND Phon_Deleted is null


        --Open Cursor & fetch 1st row into variables
        OPEN CompanyFaxLinkCursor
        FETCH NEXT FROM CompanyFaxLinkCursor INTO @cf_PLink_LinkID, @cf_PLink_PhoneId, @cf_PLink_CreatedBy, @cf_PLink_CreatedDate,
                                      @cf_PLink_UpdatedDate, @cf_PLink_TimeStamp, @cf_PLink_EntityID, @cf_PLink_RecordID, @cf_PLink_Type


        --Fetch successful
        --Check for a new row
        WHILE @@FETCH_STATUS = 0
        BEGIN

        EXEC @PhoneLinkID = crm_next_id 10208
        EXEC @PhoneId = crm_next_id 14


            INSERT INTO PhoneLink
            (
                PLink_LinkID, PLink_PhoneId, PLink_CreatedBy, PLink_CreatedDate, PLink_UpdatedDate,
                PLink_TimeStamp, PLink_EntityID, PLink_RecordID, PLink_Type
            )
            VALUES
            (
                @PhoneLinkID, @PhoneId, '1', GETDATE(), GETDATE(),
                GETDATE(), @cf_PLink_EntityID, @cf_PLink_RecordID, 'Fax'
            )           


        --Get next available row into variables
        FETCH NEXT FROM CompanyFaxLinkCursor INTO @cf_PLink_LinkID, @cf_PLink_PhoneID, @cf_PLink_CreatedBy, @cf_PLink_CreatedDate,
                                      @cf_PLink_UpdatedDate, @cf_PLink_TimeStamp, @cf_PLink_EntityID, @cf_PLink_RecordID, @cf_PLink_Type

        END

        CLOSE CompanyFaxLinkCursor
        DEALLOCATE CompanyFaxLinkCursor



------- Company Phone (Fax) Cursor --------------------------------------------------
        -- Declare Variables
        DECLARE @cf_Phon_PhoneId NVARCHAR(30)
        DECLARE @cf_Phon_Number NVARCHAR(30)
        DECLARE @cf_Phon_CreatedBy NVARCHAR(30)
        DECLARE @cf_Phon_CreatedDate NVARCHAR(30)
        DECLARE @cf_Phon_UpdatedBy NVARCHAR(30)
        DECLARE @cf_Phon_UpdatedDate NVARCHAR(30)
        DECLARE @cf_Phon_TimeStamp NVARCHAR(30)


        --Declare Cursor
        DECLARE CompanyFaxCursor CURSOR FOR
        SELECT Phon_PhoneId, Phon_Number, Phon_CreatedBy, Phon_CreatedDate, Phon_UpdatedBy, Phon_UpdatedDate, Phon_TimeStamp
        FROM Phone
        INNER JOIN PhoneLink
        ON Phon_PhoneId = PLink_PhoneId
        AND PLink_RecordID = @Comp_CompanyId
        AND Plink_EntityID = '5'
        AND Plink_Type = 'Fax'
        AND Plink_Deleted is null
        AND Phon_Deleted is null


        --Open Cursor & fetch 1st row into variables
        OPEN CompanyFaxCursor
        FETCH NEXT FROM CompanyFaxCursor INTO @cf_Phon_PhoneId, @cf_Phon_Number, @cf_Phon_CreatedBy, @cf_Phon_CreatedDate,
                                      @cf_Phon_UpdatedBy, @cf_Phon_UpdatedDate, @cf_Phon_TimeStamp


        --Fetch successful
        --Check for a new row
        WHILE @@FETCH_STATUS = 0
        BEGIN


        INSERT INTO Phone
        (
            Phon_PhoneId, Phon_Number, Phon_CreatedBy, Phon_CreatedDate, Phon_UpdatedBy, Phon_UpdatedDate, Phon_TimeStamp
        )
        VALUES
        (
            @PhoneID, @cf_Phon_Number, '1', GETDATE(), '1', GETDATE(), GETDATE()
        )


        --Get next available row into variables
        FETCH NEXT FROM CompanyFaxCursor INTO @cf_Phon_PhoneId, @cf_Phon_Number, @cf_Phon_CreatedBy, @cf_Phon_CreatedDate,
                                      @cf_Phon_UpdatedBy, @cf_Phon_UpdatedDate, @cf_Phon_TimeStamp

        END

        CLOSE CompanyFaxCursor
        DEALLOCATE CompanyFaxCursor

My table structure is as below:

Phone Link Table:

PLink_LinkID | PLink_EntityID | PLink_RecordID | PLink_Type | PLink_PhoneID
-------------------------------------------------------------------------
1            | 5              | 2              | Business   | 1
2            | 5              | 3              | Fax        | 2
3            | 5              | 2              | Fax        | 3
4            | 5              | 3              | Business   | 4

Phone Table:

 Phon_PhoneID | Phon_Number
 --------------------------
 1            | 11111111
 2            | 22222222
 3            | 33333333
 4            | 44444444

Now whenever I execute this stored procedure I will get the error shown below. I've tried swapping every Phone table with its PhoneLink table in order to insert phone first only link to company, but I still got the following error repeated twice:

Msg 547, Level 16, State 0, Procedure DuplicateCompanyInfo, Line 150 (Assumed)

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PhoneLink_PhoneId". The conflict occurred in database "CRM", table "dbo.Phone", column 'Phon_PhoneId'.

Msg 547, Level 16, State 0, Procedure DuplicateCompanyInfo, Line 160 (Assumed)

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PhoneLink_PhoneId". The conflict occurred in database "CRM", table "dbo.Phone", column 'Phon_PhoneId'.

I know it's a very straight forward error message but I can't fix it. I'm new in SQL. Please help!

Upvotes: 0

Views: 419

Answers (1)

Baked Inhalf
Baked Inhalf

Reputation: 3735

You can view all the constraints by this command: 

exec sp_helpconstraint 'mytable'

Seems like there are duplicates.

Upvotes: 1

Related Questions