Updating - Unique Key Constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating - Unique Key Constraint

Author

Topic

bdavis
Starting Member

12 Posts

Posted - 2006-06-21 : 12:13:07

I'm trying to clean-up certain Phone Types and I need to update Phone Types system wide, but certain constraints are holding me back...I'm just getting the error:

Violation of UNIQUE KEY constraint 'IX_co_customer_x_phone'. Cannot insert duplicate key in object 'co_customer_x_phone'.

The statement has been terminated. Strange thing is I can add certain statements to my where clause to find an exact customer and change their phone type, but when I want to change the phone type system wide I get this error.

update co_customer_x_phone
set cph_pht_key = '47961833-C53A-4223-8229-4453350934F7'
FROM co_customer_x_phone (NOLOCK)
LEFT JOIN co_phone_type (NOLOCK)
on pht_key = cph_pht_key
JOIN co_customer cst (NOLOCK)
ON cph_cst_key = cst.cst_key
JOIN co_phone (NOLOCK)
ON cph_phn_key=phn_key
where cph_pht_key='2E486DF9-7A16-47F6-83BA-A304746F50DE'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-21 : 13:20:13

The index IX_co_customer_x_phone does not allow you to set the cph_pht_key to '47961833-C53A-4223-8229-4453350934F7' more than once for one customer.

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-21 : 13:20:38

You are trying to insert a key that already exists. Since this key is constrained as UNIQUE, this is not allowed.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-21 : 17:12:22

I think that one of the other tables isn't rightly keyed.
Just run the select without update to examine the data.

--update co_customer_x_phone
--set cph_pht_key = '47961833-C53A-4223-8229-4453350934F7'
SELECT * --<-- only for diagnostic purposes
FROM co_customer_x_phone (NOLOCK)
LEFT JOIN co_phone_type (NOLOCK)
on pht_key = cph_pht_key
JOIN co_customer cst (NOLOCK)
ON cph_cst_key = cst.cst_key
JOIN co_phone (NOLOCK)
ON cph_phn_key=phn_key
where cph_pht_key='2E486DF9-7A16-47F6-83BA-A304746F50DE'

If you get multiple rows (you will), start uncommenting the tables one by one to see where the duplicate creeps in.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources