Fixed

PK Indexes Need to be Unique for Foreign Keys to Work

5 years ago • updated by 5 years ago • 6

Most recent iteration is throwing errors on all my foreign keys for new tables. I had to add UNIQUE to all the generated clustered PK indexes that were created to get foreign keys to connect to them as it is no longer including the PK Constraint/Index in the create table statement:

Old Generated Code:

CREATE TABLE [LaborClock]
(
[LaborClockId] INT IDENTITY (1, 1) NOT NULL ,
[TimeIn] TIME(7) NOT NULL ,
[TimeOut] TIME(7) NOT NULL

CONSTRAINT [PK_LaborClock] PRIMARY KEY CLUSTERED ([LaborClockId] ASC)
);

New Generated Code:

CREATE TABLE [LaborClock]
(
[LaborClockId] int IDENTITY (1, 1) NOT NULL ,
[TimeIn] int NOT NULL ,
[TimeOut] int NOT NULL
);
GO

CREATE CLUSTERED INDEX [PK_LaborClock] ON [LaborClock]
(
[LaborClockId] ASC
)

GO

To make this work, need to add a unique keyword to the primary key's index otherwise you can't reference it in other foreign keys:

CREATE TABLE [LaborClock]
(
[LaborClockId] int IDENTITY (1, 1) NOT NULL ,
[TimeIn] int NOT NULL ,
[TimeOut] int NOT NULL
);
GO

CREATE UNIQUE CLUSTERED INDEX [PK_LaborClock] ON [LaborClock]
(
[LaborClockId] ASC
)

GO

Vote

Answer

Fixed

5 years ago

Hi, the new version with bug fix has been rolled.

Thank you for your assistance,

SqlDBM Team

Replies 6
Oldest first
- Newest first
- Oldest first

Under review

5 years ago

Thank you for feedback, we will review this ticket soon

Thank you for your assistance,

SqlDBM Team

5 years ago

Dear User,

Sorry for the inconvenience. But looks like PK in your tables are not having "As constraint" option is checked

Can you please make sure that "As constraint" option is checked for PK index as shown below:

Please feel free to contact us in case you have any further questions.

Thanks,

Team - SqlDBM

5 years ago

For some reason, I am just getting an image placeholder on the photo you have.

From poking about - this is found by clicking the table, going to the indexes list, finding the entry for any identity columns, and then switching from as index to as constraint correct?

Was there some sort of bug in a new version that would have caused all our primary keys in every table to have this set incorrectly? It appears that the default behaviour when creating a new table and setting the identity checkmark on a new column sets it to the "As Constraint" option by default but for some reason in a recent update, all ours came out as "As Index"!?

This does seem to resolve the issue if I go back into all the tables and do this, but I'm a little lost as to what caused the issue in the first place given that all was well till recently. I'm not loving the idea of doing this 50 times but I guess it's better to have found this out now than when we get to 300 tables!?