Reference to a table that is TPT (always update)
Posted: Mon 09 May 2022 13:43
Version: 6.12.1419
I detected a problem, to occur was like this:
I add the first 3 tables for me to create the TPT
Looking like this:
I find a bug here as I create the TPT and then remove the references to the Animal table it doesn't update the diagram, because the AnimalId fields are still visible. But if I save and then reopen the model it disappears.
But something that does not impact. But I spent a few hours thinking I was doing something wrong.
Then I add another TableRef table to the model.
With that it does not create the reference to the Dog table.
And it stays in the update loop.
If I add all the tables before creating the TPT it adds the reference correctly.
But it stays in the update loop too.
I detected a problem, to occur was like this:
Code: Select all
CREATE DATABASE TestED
GO
USE [TestED]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Animal](
[AnimalId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Animal] PRIMARY KEY CLUSTERED
(
[AnimalId] 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]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Cat](
[AnimalId] [int] NOT NULL,
[FieldB] [int] NOT NULL,
CONSTRAINT [PK_Cat] PRIMARY KEY CLUSTERED
(
[AnimalId] 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]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Dog](
[AnimalId] [INT] NOT NULL,
[Field1] [NVARCHAR](10) NOT NULL,
CONSTRAINT [PK_Dog] PRIMARY KEY CLUSTERED
(
[AnimalId] 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]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TestRef](
[TestRefId] [INT] IDENTITY(1,1) NOT NULL,
[TesAB] [NVARCHAR](50) NOT NULL,
[AnimalId_Dog] [INT] NOT NULL,
CONSTRAINT [PK_TestRef] PRIMARY KEY CLUSTERED
(
[TestRefId] 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]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Cat] WITH CHECK ADD CONSTRAINT [FK_Cat_Animal] FOREIGN KEY([AnimalId])
REFERENCES [dbo].[Animal] ([AnimalId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Cat] CHECK CONSTRAINT [FK_Cat_Animal]
GO
ALTER TABLE [dbo].[Dog] WITH CHECK ADD CONSTRAINT [FK_Dog_Animal] FOREIGN KEY([AnimalId])
REFERENCES [dbo].[Animal] ([AnimalId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Dog] CHECK CONSTRAINT [FK_Dog_Animal]
GO
ALTER TABLE [dbo].[TestRef] WITH CHECK ADD CONSTRAINT [FK_TestRef_Dog] FOREIGN KEY([AnimalId_Dog])
REFERENCES [dbo].[Dog] ([AnimalId])
GO
ALTER TABLE [dbo].[TestRef] CHECK CONSTRAINT [FK_TestRef_Dog]
GOLooking like this:
I find a bug here as I create the TPT and then remove the references to the Animal table it doesn't update the diagram, because the AnimalId fields are still visible. But if I save and then reopen the model it disappears.
But something that does not impact. But I spent a few hours thinking I was doing something wrong.
Then I add another TableRef table to the model.
With that it does not create the reference to the Dog table.
And it stays in the update loop.
If I add all the tables before creating the TPT it adds the reference correctly.
But it stays in the update loop too.
