Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Violation of Primary Key constraint

Status
Not open for further replies.

creativeedg10

Technical User
Feb 16, 2011
10
US
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,
 
You are trying to insert values from one table into another or just a single value?

Simi
 
I am just inserting a single value. I'm simply adding a new record consisting of various fields.
 
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
Code:
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
 
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
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top