Primary Key error, cannot add duplicate entry

Question

0.00/5 (No votes)

See more:

Hello,

I have imported a CSV file into SQL, the wizard has added this as a new table.

I am trying to run a SQL Statement to insert the data from the imported table into the one I need to use in the new data table.

However I am getting the following error.

Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'PK_Customer'. Cannot insert duplicate key in object 'dbo.Customer'. The duplicate key value is (105).
The statement has been terminated.

This is my statement:

SQL

INSERT INTO dbo.Customer
(custID,lastName,firstName,title,address1,address2,address3,postCode,email,fax)
SELECT ACCOUNT,SURNAME,FORENAMES,TITLE,ADD1,ADD2,ADD3,POSTCODE,EMAIL,FAX
FROM dbo.[NCCUST CSV FORMAT PFS 9.1.14];

Any pointers would be great :)

Thanks

Posted 10-Jan-14 0:18am

Glen Childs

Updated 30-Jan-18 19:56pm

Add a Solution

5 solutions

Solution 1

if you want enter the duplicate (Records)entry then remove the Primary key. alter your table like this type.

SQL

ALTER TABLE Table_Name
DROP CONSTRAINT PrimaryKey_Constaint

Posted 10-Jan-14 0:23am

joginder-banger

Comments

Glen Childs 10-Jan-14 6:27am

I couldn't drop the PK - Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Customer'. Cannot insert duplicate key in object 'dbo.Customer'. The duplicate key value is (105).
The statement has been terminated.
Msg 3725, Level 16, State 0, Line 6
The constraint 'PK_Customer' is being referenced by table 'Accounts', foreign key constraint 'FK_CustomerID'.
Msg 3727, Level 16, State 0, Line 6
Could not drop constraint. See previous errors

joginder-banger 10-Jan-14 6:31am

your primary key Referenced foreign key... Remove first Reference key.

Glen Childs 10-Jan-14 7:00am

Thanks removing them worked, but how can I add the keys back? As my C# app isnt now able to access the data in those tables.

Solution 2

If you do not want the CustID to be filled from csv file then you can make that field as an identity field and use the following query.

SQL

INSERT INTO dbo.Customer
(lastName,firstName,title,address1,address2,address3,postCode,email,fax)
SELECT SURNAME,FORENAMES,TITLE,ADD1,ADD2,ADD3,POSTCODE,EMAIL,FAX
FROM dbo.[NCCUST CSV FORMAT PFS 9.1.14];

Or else you need to delete the primary key, before deleting the primary key constraint, you have to take out the referential integrity constraint and then drop the primary constraint.

Posted 10-Jan-14 0:31am

Deviprasad Das

Solution 3

Remove the Foreign key like this type.

SQL

ALTER TABLE Table_Name
DROP FOREIGN KEY Foreign_Key_ID

Posted 10-Jan-14 0:33am

joginder-banger

Solution 5

violation of PRIMARY KEY constraint 'PK_LPresent', cannot insert dublicate key in object 'dbo.LPresent'. The dublicate key value is (2), the statement has been terminated Translate to somalia

Posted 30-Jan-18 19:56pm

Member 13653154

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-01-10T11:52:00

Primary key (PK)[^] is to guarantee unique data that are frequently defined on an identity column.

A relational database[^] uses PK

Wiki wrote:
In the relational model, each table schema must identify a column or group of columns, called the primary key, to uniquely identify each row. A relationship can then be established between each row in the table and a row in another table by creating a foreign key, a column or group of columns in one table that points to the primary key of another table. The relational model offers various levels of refinement of table organization and reorganization called database normalization. (See Normalization below.) The database management system (DBMS) of a relational database is called an RDBMS, and is the software of a relational database.

Do not remove any PK if you don't want to lose relationships between tables!

If you want to add customers from other data set, please make sure that customer does not exists in the destanation table. You can achieve that using IN clause[^].

SQL

INSERT INTO DestTable (<fields collection>)
SELECT <fields collection>
FROM SourceTable
WHERE DestTable.ID NOT IN (SELECT ID FROM SourceTable)

or use EXISTS statement[^].