The INSERT statement conflicted with the FOREIGN KEY constraint in table insert

Question

I have the following query that I try to execute:

DECLARE @SessionId UNIQUEIDENTIFIER = NEWID();

INSERT INTO [security].[EventLog] ([Id], [Created], [Updated], [Session],
                                   [IdentityGlobalId], [EventType], [Description])
    SELECT 
        NEWID(), GETUTCDATE(), NULL, @SessionId,
        r.Id, 4 AS [Change], -- RoleRemoved
        'Removed role: ' + CAST(ISNULL(r.Region,'') AS VARCHAR(8)) + ':'+ CAST(ISNULL(r.CustomerNumber,'') AS VARCHAR(8)) + ':' + CAST(ISNULL(r.RoleId,'') AS VARCHAR(8))
    FROM 
        @toRemove r

But when I try that, I get the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EventLog_Identities_IdentityGlobalId". The conflict occurred in database "master", table "security.Identities", column 'GlobalId'.

I know that before data can be inserted, the value in the foreign key field must exist in the other table first, in this case the Identities-table. The thing is that when I look into the identities-table, I can see the value right there.

So why do I still get this error?

DDL for the tables:

CREATE TABLE [security].[Identities]
(
    [GlobalId] [uniqueidentifier] NOT NULL,
    [Created] [datetimeoffset](7) NULL,
    [Updated] [datetimeoffset](7) NULL,

    CONSTRAINT [PK_Identities] 
        PRIMARY KEY CLUSTERED ([GlobalId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [security].[EventLog]
(
    [Id] [uniqueidentifier] NOT NULL,
    [Created] [datetimeoffset](7) NULL,
    [Updated] [datetimeoffset](7) NULL,
    [Session] [uniqueidentifier] NOT NULL,
    [IdentityGlobalId] [uniqueidentifier] NOT NULL,
    [EventType] [nvarchar](max) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [Published] [datetimeoffset](7) NULL,

    CONSTRAINT [PK_EventLog] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [security].[EventLog] WITH CHECK 
    ADD CONSTRAINT [FK_EventLog_Identities_IdentityGlobalId] 
        FOREIGN KEY([IdentityGlobalId]) REFERENCES [security].[Identities] ([GlobalId])
                ON DELETE CASCADE
GO

ALTER TABLE [security].[EventLog] CHECK CONSTRAINT [FK_EventLog_Identities_IdentityGlobalId]
GO

Please can you share with us the DDL of the EventLog table and the Identities table. — VBoka, Mar 20 '20 at 14:19
And check this: https://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint-sql-server — VBoka, Mar 20 '20 at 14:20

score 0 · Answer 1 · answered Mar 20 '20 at 15:12

Thank you for sharing extra details with us(DDL of two tables) After you shared that I started to test and I realized that your problem is actually with the value that is in your @toRemove variable.

You are selecting value from r.Id (r is alias for @toRemove) and you are trying to insert that value in the IdentityGlobalId column of the table EventLog.

That value does not exist in the table Identities in the column GlobalId and that is why your FK constraint is throwing error.

I hope this explanation will help you. I also crated a small example where I create two tables you shared and then I insert one row in the table Identities. Then I select random values and value from column Identities.GlobalId to insert into table EventLog.

Here is a demo of that where you can see that EventLog.IdentityGlobalId is same as Identities.GlobalId and that is the only way your insert will go through - if there is the same value in the Identities.GlobalId column as the one you are trying to insert into EventLog.IdentityGlobalId column.

Hi @Bryan , I do not understand, you have 2000+ points and how is it you do not give a feedback to an answer to your question ? — VBoka, Mar 23 '20 at 17:56

The INSERT statement conflicted with the FOREIGN KEY constraint in table insert

1 Answers1