0
Not a bug

Duplicate constraints (PK and UQI)

ecoffman 2 years ago updated by Olesya 1 year ago 6

Here is an example of the duplicate constraint (UQI_Status_Name) that this tool is now producing.


CREATE TABLE [HubLOV].[Status](

[StatusId] int IDENTITY (1000, 1) NOT NULL , 

[Name] nvarchar(25) NOT NULL , 

[ActiveFlag] bit NOT NULL , 

[MdsCode] varchar(250) NOT NULL ,
CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED ([StatusId] ASC), 

CONSTRAINT [UQI_Status_Name] UNIQUE NONCLUSTERED ([Name] ASC));

GO

CREATE UNIQUE NONCLUSTERED INDEX [UQI_Status_Name] ON [HubLOV].[Status] ( [Name] ASC )
GO

When I delete the table and recreate it, the duplication seems to go away.

Here is also a table where the PK and the UQI is duplicate:

CREATE TABLE [HubLOV].[SpecificResultCategory]( 

[SpecificResultCategoryId] int IDENTITY (1000, 1) NOT NULL , 

[Name] nvarchar(60) NOT NULL , 

[ActiveFlag] bit NOT NULL ,
CONSTRAINT [PK_SpecificResultCategory] PRIMARY KEY NONCLUSTERED ([SpecificResultCategoryId] ASC), 

CONSTRAINT [UQI_SpecificResultCategory_Name] UNIQUE CLUSTERED ([Name] ASC));

GO

CREATE NONCLUSTERED INDEX [PK_SpecificResultCategory] ON [HubLOV].[SpecificResultCategory] ( [SpecificResultCategoryId] ASC )
GO
CREATE UNIQUE CLUSTERED INDEX [UQI_SpecificResultCategory_Name] ON [HubLOV].[SpecificResultCategory] ( [Name] ASC )
GO

Under review

Thank you for feedback, we will review this ticket soon

Thank you for your assistance,

SqlDBM Team

Accepted

Dear User,

Sorry for the inconvenience.

Could you provide share link to revision with broken model and screenshot with Index Property Editor (is both highlighted options is checked?)

- Is this model imported using Reverse Engineering or created manually?

Please provide above details, so we can detect the issue and resolve it soon.

Thanks

Team - SqlDBM

We created the db manually. 

To fix this, for all the tables, I manually switched to "generate index" and then back to "as constraint". This fixed all the issues for all the tables.