Solved

Violation of PRIMARY KEY constraint in UPDATE statement

Posted on 2006-06-23
15
649 Views
Last Modified: 2006-11-18
I am trying to update a table and I keep getting the "Violation of PRIMARY KEY constraint 'PK_tblAddress_List'. Cannot insert duplicate key in object 'tblAddress_List'. The statement has been terminated." error message.

I made a temporary table of tblAddress_List without any primary keys/constraints and the update worked beautifully.  This is the code I used:

update tblAddress_List
set listid = 344
from tblAddress, tblAddress_List
where tblAddress.id=tblAddress_List.addressid and tblAddress.active = 0

Since I'm a beginner, can someone tell me how I can update the table without getting the error?  Getting codes would help.
0
Comment
Question by:llputney
  • 7
  • 6
  • 2
15 Comments
 
LVL 5

Expert Comment

by:MageDribble
Comment Utility
check to see if 344 already exists in the tblAddress_List table.  Primary Key means you can not have duplicate values - each value in that column must be unique.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
Comment Utility
Is listid the primary key of the adddress_list table??

If it is the unique id, why are you trying to update multiple records with the same id (344)?


What exactly are you trying to do, and what is your schema for the two tables?

I also recommend working on using the following SQL syntax instead of the old non-ansi way you used above.

update tblAddress_List
set listid = 344
from tblAddress a
join tblAddress_List al
on tblAddress.id=tblAddress_List.addressid
and tblAddress.active = 0
0
 

Author Comment

by:llputney
Comment Utility
I checked tblAddress_List table and found one record with "344" and changed it.  Re-ran the statement and still got the same error.

tblAddress_List has PK_tblAddress_List index with dependency on fields: addressid and listid

tblAddress has PK_tblAddress index with dependency on field id (same as addressid from tblAddress_List)
0
 

Author Comment

by:llputney
Comment Utility
Ok...there are two tables:

tblAddress is like an address book that includes an id column, active colum means whether it is considered an active record or delete.

tblAddress_List has an addressid column that is the same value as the id column from tblAddress and also has a listid field which is a list that a person subscribes to.

I need to restore all the deletes (change all active fields from 0 to 1 - that's easy), but before I change all the active status, I need to change all the listid to 344 to subscribe to a certain list before I change the active status.
0
 
LVL 5

Expert Comment

by:MageDribble
Comment Utility
If you have the same value in AddressID and ListID, it will give you the primary key error.
0
 
LVL 5

Expert Comment

by:MageDribble
Comment Utility
ListID is first, AddressID is second

344, 123  - this is ok
344, 123  - this is not okay becasue it already exists in the database.  Primary Key violation!
344, 100  - this is ok b/c it doesn't already exist

If your primary key exists against 2 columns then you can not have the same value in the database twice.  The above example shows a primary key violation.
0
 

Author Comment

by:llputney
Comment Utility
AddressID and ListID do not have the same value.

AddressID in tblAddress_List and ID in tblAddress are the same values - same fields, same concept - just different field name
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:llputney
Comment Utility
MageDribble - I APPRECIATE your prompt responses, but I still don't understand.  Please bear with me!!!
0
 
LVL 5

Expert Comment

by:MageDribble
Comment Utility
things are getting pretty busy at work so I may not be able to respond for a few hours.  Another expert should be able to finish this question for you :)
0
 
LVL 5

Expert Comment

by:MageDribble
Comment Utility
Okay, I'm back.

Let's start from the top because I'm a little lost after that break :)

We are trying to update a table called "tblAddress_List" but we are experiencing a primary key constraint error when we update the table.  tblAddress_List has a primary key of 2 fields (AddressID, ListID).  Correct?

The primary key (also called a unique index) prevents multiple instances in the database.  If we had a sample table "MyTable" which had a primary key on the "MyField" then the following is valid:

MyField
---------
1
2
3

However, this is NOT valid b/c duplicate values are not allowed in a primary key

MyField
--------
1
1    <-- This would raise the primary key constraint
2
3

If you are updating your ListID field to 344 then you need to check the AddressID field to see if any duplicates are in that field.

AddressID                  ListID
---------                  ---------
123                        100
123                        101
124                        100
125                        100


These are all valid in your current system because no duplicates exists.  However, when you run your update query you'll return the following results:

AddressID                  ListID
---------                  ---------
123                        344
123                        344                        <-- Primary constraint here b/c duplicates the record above this
124                        344
125                        344

Did this help?
0
 
LVL 5

Expert Comment

by:MageDribble
Comment Utility
Sorry my wording on the MyField example was wierd so I changed it....

The primary key (also called a unique index) prevents multiple instances in the database.  If we had a sample table "MyTable" which had a primary key on the "MyField" then the following is valid:

MyField
---------
1
2
3

However, the example below is NOT valid b/c duplicate values are not allowed in a primary key

MyField
--------
1
1    <-- This would raise the primary key constraint
2
3
0
 

Author Comment

by:llputney
Comment Utility
I realized the problem and after seeing a picture of it helped make sense of it all.  Each AddressId may have more than one ListId, but the same AddressId cannot have two or more of the same ListId.

For example:

This is okay:

Address ID                                              ListID
------------                                              -------
1                                                           100
1                                                           101
1                                                           104
2                                                           101
3                                                           100
3                                                           101
3                                                           104
4                                                           101

This is not okay:

Address ID                                              ListID
------------                                              -------
1                                                           100
1                                                           100
1                                                           100
2                                                           101
3                                                           104
3                                                           104
3                                                           104
4                                                           101

I also realized that I'm supposed to leave the ListID as is and just INSERT (as opposed to UPDATE) a new row with the same AddressID and new ListID number.  

This changes the WHOLE ballgame and now I'm stumped AGAIN!!!

Here is what I'm trying to do -

Trying to do an INSERT with references to two tables.  I need to ADD a new listid number '344' (in tbl B) where active = 0 (in tbl A) without changing any of the originial ListID's in tbl B.
 
I need it to come out like this:

Table A                              Table B
---------                              ----------
ID          Active                    AddressID       ListID
1               0                            1               100
2               0                            1               101
3               0                            1               104
4               0                            1               344
                                               2               101
                                               2               344  
                                               3               100
                                               3               101
                                               3               104
                                               3               344
                                               4               101
                                               4               344


I tried doing something like this:

insert into tblAddress_List (listid) values (344)
select a.id, a.active, b.listid
from tbladdress a, tbladdress_list b
where a.id = b.addressid
and a.active = 0

But got this error message:
Cannot insert the value NULL into column 'addressid', table 'tempdb.dbo.tblAddress_List'; column does not allow nulls. INSERT fails.
The statement has been terminated.
     

Can ya help?
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
Comment Utility
Try this...

INSERT INTO tblAddress_List (AddressID, ListID)
SELECT ID, 344
FROM tbladdress a
WHERE Active = 0
AND NOT EXISTS(SELECT * FROM tblAddress_List WHERE AddressID = a.id AND ListID = 344)

0
 
LVL 5

Accepted Solution

by:
MageDribble earned 500 total points
Comment Utility
The below query will insert the ListID (always 344) and the address ID from the table.

INSERT INTO tblAddress (listID, AddressID)
VALUES
SELECT 344, b.addressID
FROM tbladdress a, tbladdress_list b
WHERE a.id = b.addressid AND a.active = 0
0
 

Author Comment

by:llputney
Comment Utility
Thanks for all your efforts!! Finally got it figured out through another post.  This is what I did:

insert into tblAddress_List (listid, addressid)
select 344, a.id from tbladdress a
where a.id in (select distinct addressid from tbladdress_list where addressid = a.id)
and a.active = 0
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now