CODEWITHSUNDEEP
sql-serverfunctionparametersprimary-keystar-schema
dmaria
dmaria

Reputation: 1

Violation of Primary Key Constraint - SQL Server

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

Upvotes: 0

Views: 3260

Answers (2)

Slava Murygin
Slava Murygin

Reputation: 1955

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);

Upvotes: 1

Andreas Sundström
Andreas Sundström

Reputation: 218

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.

Upvotes: 1

Related Questions