主键错误，无法添加重复条目 [英] Primary Key error, cannot add duplicate entry

问题描述

您好，



我已将CSV文件导入SQL，向导已将此文件添加为新表。



我正在尝试运行一个SQL语句，将导入表中的数据插入到我需要在新数据表中使用的数据中。



但是我收到以下错误。

消息2627，等级14，状态1，行4 
违反PRIMARY KEY约束'PK_Customer'。无法在对象'dbo.Customer'中插入重复键。重复键值是（105）。 
声明已被终止。

这是我的陈述：

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

任何指针都会很棒:)



谢谢

解决方案

主键（PK） [ ^ ]是为了保证唯一的数据通常在标识列上定义。



关系数据库 [ ^ ]使用PK

< blockquote class =FQ>

Wiki写道：

在关系模型中，每个表模式必须标识一列或一组列，称为主键，以唯一标识每一行。然后，可以通过在一个表中创建指向另一个表的主键的外键，一列或一组列，在表中的每一行与另一个表中的一行之间建立关系。关系模型提供了表组织和重组的各种级别的细化，称为数据库规范化。 （请参阅下面的规范化。）关系数据库的数据库管理系统（DBMS）称为RDBMS，是关系数据库的软件。

如果您不想丢失表之间的关系，请不要删除任何PK！



如果您想从其他数据中添加客户设置，请确保destanation表中不存在客户。您可以使用 IN子句 [ ^ ]。

  INSERT   INTO  DestTable（< fields collection>）
  SELECT < fields collection> 
  FROM  SourceTable 
  WHERE  DestTable.ID  NOT   IN （ SELECT  ID  FROM  SourceTable）

或使用 EXISTS声明 [ ^ ]。

如果要输入重复（记录）条目，则删除主键。像这种类型改变你的表。

  ALTER   TABLE  Table_Name 
  DROP   CONSTRAINT  PrimaryKey_Constaint 

如果您不希望CustID为从csv文件填充然后你可以将该字段作为标识字段并使用以下查询。

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

否则你需要删除主键，删除主键con之前straint，你必须取出参照完整性约束，然后删除主要约束。

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:

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

解决方案

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[^].

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

or use EXISTS statement[^].

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

ALTER TABLE Table_Name
DROP CONSTRAINT PrimaryKey_Constaint

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.

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.

这篇关于主键错误，无法添加重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！