1

Short version

Q: Is there a bug like kb4503379 in SQL 2019 v15.0.2095.3.

A: Yes, seems like it. Install v15.0.4236.7 and the problem goes away.

Original Long version

I understand foreign keys and referential integrity, but I'm hitting a problem like this bug where DELETEs can fail when they should succeed. However, that was for SQL 2016 & 2017 and I'm on 2019. Are there similar bugs on SQL 2019? I'm using SQL 2019 Developer edition v15.0.2095.3. (Spoiler: upgrading fixed it)

My problem is I'm hitting this error, but there is no data in the table causing the conflict. If I drop other tables then the problem goes away, just like in the bug mentioned above.

Msg 547, Level 16, State 0, Line 11
The DELETE statement conflicted with the REFERENCE constraint "FK_p0010_vtr_OutputDistriktsbolag_Reference". The conflict occurred in database "Tenant_XXXX", table "planData.p0010_vtr_OutputDistriktsbolag", column 'ReferenceId'.

I know, it sounds like I'm just not looking correctly, but there's really no data that should cause the constraint violation.

There are two main tables of interest dbo.Reference (parent table) and planData.p0010_vtr_OutputDistriktsbolag (child table). When I run this SQL I get the error:

begin tran 

-- delete the referencing data (several thousand rows)
delete from planData.p0010_vtr_OutputDistriktsbolag 
where ReferenceId = 14

-- check it's not there. This returns 0 rows. 
select * from planData.p0010_vtr_OutputDistriktsbolag 
where ReferenceId = 14

-- But this fails with FK error!?
delete from dbo.Reference
where ReferenceId = 14

Table definitions are further below, but dbo.Reference has an identity primary key of ReferenceId and planData.p0010_vtr_OutputDistriktsbolag has a foreign key from its ReferenceId column to dbo.Reference.ReferenceId. So far nothing strange.

However, there are a total of 404 tables similar to planData.p0010_vtr_OutputDistriktsbolag that also reference dbo.Reference.ReferenceId. If I drop all of them except for planData.p0010_vtr_OutputDistriktsbolag and then run the statements above THEN NO ERROR OCCURS! This makes me think it's a problem just like kb4503379.

Some other points:

  • There is no other activity on the db, this is running on a development db on my dev machine.
  • both tables are system-versioned temporal tables with server-generated history tables. I tried removing SYSTEM_VERSIONING and that didn't appear to make a difference.
  • I tried setting compatibility level to 130 and that didn't appear to make a difference
  • I don't know how to see the query plan for my DELETE that fails so I can't check if the same problem as in that SQL bug is happening now. Any tips?
  • I've run a DBCC CHECKDB and ALTER TABLE x REBUILD on the two tables, with no change to the outcome.
  • As you can see, one table is in dbo schema and the other in planData.
  • I've double-checked there are no other tables with very similar-looking names and no other foreign keys with very similar-looking names, so it's not just a name confusion problem. Or not one I can see.

I should get SQL Server updates via windows updates but I see I'm not on the latest version so will try to fix that now.

Table create scripts are below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Reference](
    [ReferenceId] [int] IDENTITY(1,1) NOT NULL,
    [PlanId] [int] NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [Description] [nvarchar](max) NULL,
    [EditUserId] [nvarchar](100) NOT NULL,
    [SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Reference] PRIMARY KEY CLUSTERED 
(
    [ReferenceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Reference_History])
)
GO

ALTER TABLE [dbo].[Reference]  WITH CHECK ADD  CONSTRAINT [FK_Reference_Plan] FOREIGN KEY([PlanId])
REFERENCES [dbo].[Plan] ([PlanId])
GO

ALTER TABLE [dbo].[Reference] CHECK CONSTRAINT [FK_Reference_Plan]
GO

CREATE TABLE [planData].[p0010_vtr_OutputDistriktsbolag](
    [ReferenceId] [int] NOT NULL,
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [d_Distriktsbolag_Id] [int] NOT NULL,
    [d_KontoDistriktsbolagen_Id] [int] NOT NULL,
    [d_Period_Id] [int] NOT NULL,
    [d_Specifikation_Id] [int] NOT NULL,
    [DecimalValue] [decimal](38, 18) NOT NULL,
    [EditLogId] [nvarchar](100) NOT NULL,
    [SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    [ClientRowId] [int] NULL,
 CONSTRAINT [PK_p0010_vtr_OutputDistriktsbolag] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UQ_p0010_vtr_OutputDistriktsbolag_ReferenceAndDimensions] UNIQUE NONCLUSTERED 
(
    [ReferenceId] ASC,
    [d_Distriktsbolag_Id] ASC,
    [d_KontoDistriktsbolagen_Id] ASC,
    [d_Period_Id] ASC,
    [d_Specifikation_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [planData].[p0010_hvtr_OutputDistriktsbolag])
)
GO

ALTER TABLE [planData].[p0010_vtr_OutputDistriktsbolag]  WITH CHECK ADD  CONSTRAINT [FK_p0010_vtr_OutputDistriktsbolag_Distriktsbolag] FOREIGN KEY([d_Distriktsbolag_Id])
REFERENCES [planData].[p0010_d_Distriktsbolag] ([Id])
GO

ALTER TABLE [planData].[p0010_vtr_OutputDistriktsbolag] CHECK CONSTRAINT [FK_p0010_vtr_OutputDistriktsbolag_Distriktsbolag]
GO

ALTER TABLE [planData].[p0010_vtr_OutputDistriktsbolag]  WITH CHECK ADD  CONSTRAINT [FK_p0010_vtr_OutputDistriktsbolag_KontoDistriktsbolagen] FOREIGN KEY([d_KontoDistriktsbolagen_Id])
REFERENCES [planData].[p0010_d_KontoDistriktsbolagen] ([Id])
GO

ALTER TABLE [planData].[p0010_vtr_OutputDistriktsbolag] CHECK CONSTRAINT [FK_p0010_vtr_OutputDistriktsbolag_KontoDistriktsbolagen]
GO

ALTER TABLE [planData].[p0010_vtr_OutputDistriktsbolag]  WITH CHECK ADD  CONSTRAINT [FK_p0010_vtr_OutputDistriktsbolag_Period] FOREIGN KEY([d_Period_Id])
REFERENCES [planData].[p0010_d_Period] ([Id])
GO

ALTER TABLE [planData].[p0010_vtr_OutputDistriktsbolag] CHECK CONSTRAINT [FK_p0010_vtr_OutputDistriktsbolag_Period]
GO

ALTER TABLE [planData].[p0010_vtr_OutputDistriktsbolag]  WITH CHECK ADD  CONSTRAINT [FK_p0010_vtr_OutputDistriktsbolag_Reference] FOREIGN KEY([ReferenceId])
REFERENCES [dbo].[Reference] ([ReferenceId])
GO

ALTER TABLE [planData].[p0010_vtr_OutputDistriktsbolag] CHECK CONSTRAINT [FK_p0010_vtr_OutputDistriktsbolag_Reference]
GO

ALTER TABLE [planData].[p0010_vtr_OutputDistriktsbolag]  WITH CHECK ADD  CONSTRAINT [FK_p0010_vtr_OutputDistriktsbolag_Specifikation] FOREIGN KEY([d_Specifikation_Id])
REFERENCES [planData].[p0010_d_Specifikation] ([Id])
GO

ALTER TABLE [planData].[p0010_vtr_OutputDistriktsbolag] CHECK CONSTRAINT [FK_p0010_vtr_OutputDistriktsbolag_Specifikation]
GO
3
  • Can post the estimated execution plan for the delete batch? Also the DDL you posted is incomplete: there are a large number of undefined referenced tables Jul 27 at 22:56
  • Ah yes, I was trying to get the Actual plan but the error prevented it. Sadly now that I've upgraded the problem has gone away and I don't have an older engine to test it on, and probably the bugfix causes the plan to differ. The DDL of the 403 other referencing tables is the same as for p0010_vtr_OutputDistriktsbolag but with differing int columns d_XXX_Id. Now I've confirmed it's fixed with 15.0.4236.7 there seems little point scripting & posting it.
    – Rory
    Jul 28 at 19:28
  • Probably is still, it's worth documenting a bug fix Jul 28 at 19:32

1 Answer 1

0

I installed the latest cumulative update & security update KB5014353. Now I'm on version 15.0.4236.7 the problem is gone.

I don't know what the bug reference is for this particular issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.