Violation of Primary Key Constraint - SQL Server

Question

In my SQL Server, I've created a database and I am trying to export the values from the 2 tables listed below into a new table that I created DM_Location within the database.

But I keep getting this Error

Violation of PRIMARY KEY constraint 'PK__DM_Locat__BA8F50140E575579'. Cannot insert duplicate key in object 'dbo.DM_Location'. The duplicate key value is (1).

My code:

INSERT INTO DM_Location (Authority_Id, FacilityAddress, FacilityPostcode)
    SELECT AuthorityId, FacilityAddress, FacilityPostcode
    FROM dbo.2015_2016 
    UNION 
    SELECT DISTINCT AuthorityId, FacilityAddress, FacilityPostcode 
    FROM dbo.2016_2017

Any help would be appreciated! Thanks

Also - your `SELECT ... UNION` is weird - for it to **work**, any part that is combined by `UNION` must return the **same number** of columns - your first part returns 3, the second part 4 columns .... — marc_s, Jan 06 '21 at 05:26

score 1 · Answer 1 · answered Jan 06 '21 at 00:14

The problem is that you have two tables with Authority_Id value 1, but they have diferenct values in FacilityAddress or FacilityPostcode. Since you have a primary key constraint in table DM_Location this produces a Primary Key violation error.

To fix the problem you can drop the primary key constraint:

ALTER TABLE DM_Location
DROP CONSTRAINT PK__DM_Locat__BA8F50140E575579;

Of course if you want to keep a primary key on the table you will have to handle the error produced but that really depends on why you would want it.

Thanks for your response, I'll give this a go! – dmaria Jan 06 '21 at 09:49 — dmaria, Jan 06 '21 at 09:49

score 1 · Answer 2 · answered Jan 06 '21 at 01:42

It looks like your two tables have duplicates. You have to exclude them. I can guess that more recent data are "more accurate", following query might work:

INSERT INTO DM_Location (Authority_Id, FacilityAddress, FacilityPostcode)
SELECT AuthorityId, FacilityAddress, FacilityPostcode
FROM dbo.2015_2016 
WHERE AuthorityId NOT IN (SELECT AuthorityId FROM dbo.2016_2017)
UNION 
SELECT AuthorityId, FacilityAddress, FacilityPostcode 
FROM dbo.2016_2017

Also, to make sure that destination table does not already have those records and you do not want to truncate it, you can try following:

INSERT INTO DM_Location (Authority_Id, FacilityAddress, FacilityPostcode)
SELECT AuthorityId, FacilityAddress, FacilityPostcode
FROM dbo.2015_2016 
WHERE AuthorityId NOT IN (SELECT AuthorityId FROM dbo.2016_2017)
    and AuthorityId NOT IN (SELECT AuthorityId FROM DM_Location)
UNION 
SELECT AuthorityId, FacilityAddress, FacilityPostcode 
FROM dbo.2016_2017
WHERE AuthorityId NOT IN (SELECT AuthorityId FROM DM_Location);

Thanks for your response! This definitely helped, I combined the two tables with the data and managed to extract the values from there into the new table! Thanks for your help — dmaria, Jan 06 '21 at 09:48

Violation of Primary Key Constraint - SQL Server

2 Answers2