Solved

Violation of PRIMARY KEY constraint in UPDATE statement

Posted on 2006-06-23
15
655 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
ID: 16969636
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
ID: 16969689
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
ID: 16969754
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:llputney
ID: 16969814
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
ID: 16969976
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
ID: 16969997
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
ID: 16970011
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
 

Author Comment

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

Expert Comment

by:MageDribble
ID: 16970066
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
ID: 16971545
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
ID: 16971556
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
ID: 16971930
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
ID: 16972208
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
ID: 16972218
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
ID: 16972649
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

776 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