How to fix this PK constraint error?

Question

I keep getting this message when uploading the population script into CovidCases. Violation of PRIMARY KEY constraint 'PK_CovidCases'. Cannot insert duplicate key in object 'dbo.CovidCases'. The duplicate key value is (2020-09-12). How do I get all my values to upload to my tables?

Answer 1

When you define a primary key for a table, that key is an attribute that uniquely identifies a row in the table. This means, that with only date as the primary key, you anticipate one row per date. For different days the values JurisdictionID and ReportCovidCases/Death could be different. Which does sound a little odd to me.

I don't really know the data you are working with, but my guess is that you should add the JurisdictionID to the primary key. That is, each row reports the number of cases/deaths on a certain date in a certain jurisdiction. That sounds more normal to me.

Answer 2

Hi @AUBREY MCINTYRE ,

Thank you for reaching out to the Microsoft SQL Q & A Forum.

Hi @Erland Sommarskog , Thank you for your prompt response.

Hi @AUBREY MCINTYRE , as mentioned by @Erland Sommarskog , please add the JurisdictionID to the primary key. Please refer below Queries.

CREATE TABLE CovidCases (

CalendarDate DATE NOT NULL,

JurisdictionID VARCHAR(5) NOT NULL,

ReportedCovidCases INT NOT NULL,

CONSTRAINT PK_CovidCases PRIMARY KEY (CalendarDate, JurisdictionID)

);

CREATE TABLE CovidDeaths (

CalendarDate DATE NOT NULL,

JurisdictionID VARCHAR(5) NOT NULL,

ReportedCovidDeaths INT NOT NULL,

CONSTRAINT PK_CovidDeaths PRIMARY KEY (CalendarDate, JurisdictionID)

);

I hope this information helps. Please do let us know if you have any further queries.

 

Regards,

Dinesh