Update a record that has a PK

Question

I want to update an email address field on a table. My query:

UPDATE TableName
    SET emailaddress = 'someone@somewhere.com'
WHERE Per_ID = '1234'

I get this response:

Violation of PRIMARY KEY constraint 'PK_TableName'. 
Cannot insert duplicate key in object 'TableName'.

Question(s):

Is there a way to modify a field in a table that has PKs?
Assuming I have to delete the row and insert a new row, should I worry about having a different PK?
How would I do #2? :)

Thanks in advance!

Here's the create table script:

USE [Web_Production]
GO

/****** Object:  Table [dbo].[Supported_Users]    Script Date: 07/23/2013 10:48:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Supported_Users](
    [Supported_Users_ID] [int] IDENTITY(1,1) NOT NULL,
    [Per_ID] [nvarchar](32) NULL,
    [EmailAddress] [nvarchar](80) NOT NULL,
    [SerialNum] [nvarchar](20) NULL,
    [Password] [nvarchar](255) NULL,
    [OSC_LastLogonDate] [datetime] NULL,
    [OSC_TotalLogons] [int] NULL,
    [CP_LastLogonDate] [datetime] NULL,
    [CP_TotalLogons] [int] NULL,
    [IRC_LastLogonDate] [datetime] NULL,
    [IRC_TotalLogons] [int] NULL,
    [AddDate] [datetime] NOT NULL,
    [AddUser] [nvarchar](50) NOT NULL,
    [ChangeDate] [datetime] NULL,
    [ChangeUser] [nvarchar](50) NULL,
    [SupportAccess] [bit] NULL,
    [confirmationID] [nvarchar](10) NULL,
    [ForcedExpiryDate] [datetime] NULL,
    [ManualAddition] [bit] NULL,
    [Industry] [nvarchar](50) NULL,
    [Roles] [nvarchar](255) NULL,
    [OLL_Token] [nvarchar](40) NULL,
    [OLL_AddDate] [datetime] NULL,
    [ShowSupport] [bit] NULL,
    [ShowForum] [bit] NULL,
    [ShowKB] [bit] NULL,
    [ShowTraining] [bit] NULL,
    [ForceReset] [bit] NULL,
 CONSTRAINT [PK_Supported_Users] PRIMARY KEY CLUSTERED 
(
    [EmailAddress] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Supported_Users] ADD  CONSTRAINT [DF_Supported_Users_TotalLogons]  DEFAULT (0) FOR [OSC_TotalLogons]
GO

ALTER TABLE [dbo].[Supported_Users] ADD  CONSTRAINT [DF_Supported_Users_CP_TotalLogons]  DEFAULT (0) FOR [CP_TotalLogons]
GO

ALTER TABLE [dbo].[Supported_Users] ADD  CONSTRAINT [DF_Supported_Users_IRC_TotalLogons]  DEFAULT (0) FOR [IRC_TotalLogons]
GO

ALTER TABLE [dbo].[Supported_Users] ADD  CONSTRAINT [DF_Supported_Users_ManualAddition]  DEFAULT (0) FOR [ManualAddition]
GO

ALTER TABLE [dbo].[Supported_Users] ADD  CONSTRAINT [DF_Supported_Users_ShowSupport]  DEFAULT (1) FOR [ShowSupport]
GO

ALTER TABLE [dbo].[Supported_Users] ADD  CONSTRAINT [DF_Supported_Users_ShowForum]  DEFAULT (1) FOR [ShowForum]
GO

ALTER TABLE [dbo].[Supported_Users] ADD  CONSTRAINT [DF_Supported_Users_ShowKB]  DEFAULT (1) FOR [ShowKB]
GO

ALTER TABLE [dbo].[Supported_Users] ADD  CONSTRAINT [DF_Supported_Users_ShowTraining]  DEFAULT (1) FOR [ShowTraining]
GO

ALTER TABLE [dbo].[Supported_Users] ADD  CONSTRAINT [DF_Supported_Users_ForceReset]  DEFAULT (0) FOR [ForceReset]
GO

You should always worry about changing the Primary Key. It is what references everything else in other tables. — Sablefoste, Jul 23 '13 at 17:32
It *seems* like you're trying to set the `emailaddress` to something that already exists in the database, thus getting a uniqueness violation error. If you want the `emailaddress` to be mutable, it shouldn't really be part of your primary key. — voithos, Jul 23 '13 at 17:33
@voithos Bingo! The emailaddress that I'm trying to change _to_ already exists!. So I should probably delete that new record in favor of updating the old record? — user1729889, Jul 23 '13 at 17:56
@user1729889 That would depend entirely on your requirements. Maybe you want the old row updated, maybe you want the new row and delete the old one, etc. We can't know that, we can only tell you that you can't have a duplicate emailaddress on your table and `'someone@somewhere.com'` already exists — Lamak, Jul 23 '13 at 17:58

score -1 · Accepted Answer · edited Jul 23 '13 at 19:02

-1

The reason for the PK violation error message is because you are trying to update a field that already has a value that exists in the table.

Given that you know what you want to achieve, this is the SQL

DELETE TableName
WHERE  emailaddress = 'someone@somewhere.com' AND Per_ID = '1234'

UPDATE TableName
    SET emailaddress = 'someone@somewhere.com'
WHERE Per_ID = '1234

edited Jul 23 '13 at 19:02

user1729889

91
1
6

answered Jul 23 '13 at 18:18

Luis LL

2,912
2
19
21

and incidentally you shoudl never use emaill-address as a primary key http://stackoverflow.com/questions/3804108/is-email-address-a-bad-primary-key/3804174#3804174 – HLGEM Jul 23 '13 at 19:12
BAd advice. YOu do not want to delte the other key (which is probably related to someone else's person_id. WHat you need to do is to determine if the other one is in error or if this one is (typeos can cause problems here) or if the earlier one no longer uses that email address or if you have two differnt users with the same email address (organizations smetimes share them) or if you have duplicated records. Just deleteing the offending one is the worst possible thing you can do. – HLGEM Jul 23 '13 at 19:16

Update a record that has a PK

1 Answers1