Violation of Primary Key constraint

[creativeedg10]

thread183-1535233

I am referencing the thread above in regards to the following error pulled from this discussion:

"I get a error that say Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object.I have compared the tables and I not found any duplicates."

I am receiving the same error, and I too have no duplicates from my review of my table. I am entirely new to Microsoft SQL, let alone database structures. I am not familiar with the solution conversation being discussed in the thread but see it being valuable to my problem. Can someone re-clarify the solution in more simplistic terms for my understanding or provide new insight into a solution? I appreciate your time and help.

Thank you,

 

[simian336]

You are trying to insert values from one table into another or just a single value?

Simi

 

[creativeedg10]

I am just inserting a single value. I'm simply adding a new record consisting of various fields.

 

[sparkbyte]

Most Primary ket values are auto generated. (i.e. an incrimented integer value)

In your insert do not include the primary key as it will be automatically created.

Example

CREATE TABLE #test(
	[PK] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Date] DateTime NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([PK] ASC))
 
 
 Insert Into #test Values ('John',getdate())
 Insert Into #test Values ('Paul',getdate())
 Insert Into #test Values ('Mary',getdate())
 Insert Into #test Values ('Ringo',getdate())
 Insert Into #test Values ('Steve',getdate())
 Insert Into #test Values ('Mike',getdate())
 Insert Into #test Values ('Susan',getdate())
 
 Select * from #test
 
 Drop Table #test

Thanks

John Fuhrman

 

[SQLBill]

When you have a PK violation, the first thing to do is find out what is being used as the Primary Key on the table. Next, look at that value (or those values if multiple columns are used as the PK) in the values you are inserting. Do those values match what already exists?

For example:

this exists:
Col1 Col2 Col3 Col4
A B C D

You are inserting this:
INSERT INTO table1 (col1, col2, col3, col4)
VALUES ('A', 'E', ' F', 'G')

Both of those rows are unique. But if Col1 is the PK, then you will get a violation as value 'A' already exists in Col1.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[creativeedg10]

Thank you all for your help. I found the solution, and the key was being repeated somewhere else in the table. I couldn't find it at first because the different dates within a field were throwing me off, and I missed some older dates that were listed. These older dates held the repeating key.

A simple find, and I always learn best through the struggle of something so simple. haha. I appreciate your time in helping me find a solution and learn something new.

Thank you!