470,593 Members | 1,833 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

Violation of PRIMARY KEY constraint..how to solve this error?

Hai everybody :). I have problem and need help.


ERROR [23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_Table1_01'. Cannot insert duplicate key in object 'dbo.table1'.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.


How to solve this error?

It is about two tables.
Primary Key(PK) for Table1 is same value with PK for Table 2, but different names.
Data from Table1 is going to insert into Table 2.
I want to run my form but because of this error, form can not run.
I'm trying many ways to solve it but still can not solve.
I hope anyone can help me.
Thank you.
Jun 23 '08 #1
7 89667
2,878 Expert 2GB
Primary Key is a restriction that is being placed in a table to protect it's quality/integrity. It prevents from inserting multiple records with the same key. If you remove the constraint, you'll have multiple records on your table and if your search expects a single record, it will fail.

Depending on the use of your table you may remove the constrain.t Just be careful, the constraint was placed for a reason.

-- CK
Jun 24 '08 #2
Thank you CK for you answer.I had trying that way before and the error is solve. Anyway, i can not remove the constraint because it is compulsory to have in my system.

Lately, the error is disappear without drop the constraint or else. No error appear when i am debugging my form, but the form can not run. I also didnt put any break in my code. What happend here? I am confuse and weird. Anyone can explain me?

Here is example of my code to update data or row when same id is insert. Anyone can check and comment? Actually i am newbie in this field. Need much help from expert. Thank you for your all concern.

Do While drReaderSELECT.Read

If sMode = "Duplicate Record" Then

Dim SQLUPDATE As String =
"UPDATE Table2 SET id='" & strID.Trim & "', icNo='" & stricNo.Trim & "' _
WHERE id ='" & strID & "' "

Dim CMDUPDATE As New OdbcCommand(SQLUPDATE, conn)
Dim adDb As OdbcDataAdapter
adDb = New OdbcDataAdapter

CMDUPDATE.Parameters.Add("strID", OdbcType.NVarChar, 50)
CMDUPDATE.Parameters.Add("stricNo", OdbcType.NVarChar, 12)

adDb.UpdateCommand = CMDUPDATE

End If


***ID = From Table1***
***id = From Table2***
***id = ID ***
Jun 26 '08 #3
8,127 Expert 4TB
i am not sure from your code what exactly you are trying to do . Please pass more info.
Jun 27 '08 #4
Sorry taking for a long time to reply.
I want to know, why my form can not run although there is no error or warning when i'm debugging.
Jul 8 '08 #5
8,127 Expert 4TB
you need to check for existance of data in the database before inserting the same to the table.
Jul 8 '08 #6
you need to check for existance of data in the database before inserting the same to the table.
I found the answer to this question, late, but I've just run into this in a program, so...here it is.

It's a bug (but someone I'm sure would call it a feature).

If you try to update a field in your row that is part of a primary key constraint, even if it has the same value as the row you're trying to update already has, then the command assumes that you're doing an insert.

You have to modify your code to NOT update the PK fields unless you really want to do an insert.

And thus my old saying, if you try to read people's minds too much in your program, you're going to fail miserably.
Oct 21 '08 #7
I have run into this issue before as well, Like debasisdas mentioned, you can check for existence of data before you insert. Here is sample code you could use

--for insert


INSERT dbo.table1 (ID, ..........)
VALUES (123, .........)

--for update


UPDATE dbo.table1
SET ID=123
WHERE condition

hope that helps.

Aug 30 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Erland | last post: by
5 posts views Thread by mickey22 | last post: by
reply views Thread by kaleeswaran | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.