Violation of Unique Key contraint during update

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

Violation of Unique Key contraint during update

Author

Topic

crichard1983
Starting Member

11 Posts

Posted - 2009-09-18 : 12:16:59

I'm using a Unique Key constraint for the first time, and I think I may not be understanding something correctly. I have a table with a Primary key (media_format_id) that is auto-increment. Then I have 3 fields which are Foreign Keys, that I also added a Unique constraint to. I do not want any 2 records with the same value combination for those three fields (media_id, format_id, lang_id).

Here is the sp_help for my table :
IX_multimedia_formats
nonclustered, unique, unique key located on PRIMARY media_id, format_id, lang_id

PK_multimedia_formats
clustered, unique, primary key located on PRIMARY media_format_id

I use a stored procedure to create the records, and another to update them. Create works fine (unique constraint and all), but during update, I get the Violation of Unique Key contraint error every time.

Can anyone point me in the right direction ? Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-18 : 12:38:20

Look at your update!
It seems to me that your query is going to update the key values but that should, in most cases, not required.
And if there is an update required then have a look at the "new" values because they should not already exist!

No, you're never too old to Yak'n'Roll if you're too young to die.

crichard1983
Starting Member

11 Posts

Posted - 2009-09-18 : 12:47:41

Here is my update query :


UPDATE multimedia_formats
SET format_id = @format_id,
    lang_id = @lang_id,
    [filename] = @filename
WHERE media_format_id = media_format_id;

Even if I try to modify only filename (which is not part of the constraint), I get the same error.

I just did a few tests, and if the table only contains one record, I can always update correctly. As soon as there are 2 or more records in the table, I get the constraint error.

crichard1983
Starting Member

11 Posts

Posted - 2009-09-18 : 13:27:17

Ok, you were right webfred, it was my update ! Very silly error :


UPDATE multimedia_formats
		SET format_id = @format_id,
			lang_id = @lang_id,
			[filename] = @filename
	WHERE media_format_id = @media_format_id;

I forgot to put a @ in front of the where condition variable, so every time I tried to execute the update, it was trying to update ALL the records, not the one I was specifying with @media_format_id.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-19 : 03:06:36

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources