InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 06:12:03 GMT20http://www.sqlservercentral.com/Forums/Topic672664-146-1.aspxAnother suggestion is, backup the database and delete the duplicate rows so that it will remain single record instead of multiple dup records. You can easily remove dup rec if the table has a PK. Then add the UNIQUE INDEX/CONSTRAINT.Sat, 14 Mar 2009 12:24:54 GMTSusantha Bathigehttp://www.sqlservercentral.com/Forums/Topic672664-146-1.aspxI think altering the table and adding a constraint with NO CHECK option is what you need.ALTER TABLE tblTable WITH NO CHECK ADD CONSTRAINT uqConstraint UNIQUERegards,Sony Antony.Sat, 14 Mar 2009 01:37:09 GMTSony Antonyhttp://www.sqlservercentral.com/Forums/Topic672664-146-1.aspxThe other alternative put a where clause on your inserts that checks for the existance of those three values existing in the database already.Wed, 11 Mar 2009 21:26:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic672664-146-1.aspxI think your best choice here is to rename the existing table, create a new table with the old name, migrate all the existing records into the newly-created table with a "select distinct ..." then put the unique constraint/index on the new table. That way you've got the old data to refer to if necessary, although how you handle any other fields that you didn't show in your original posting will need to be addressed somehow.If there are additional fields that are different between the non-unique rows then the only way to handle it would be to somehow modify the three columns so each row does end up unique then add the unique constraint/index. Again that's going to have to be something you work out based on your business requirements.Wed, 11 Mar 2009 21:15:36 GMTGlenn Dorlinghttp://www.sqlservercentral.com/Forums/Topic672664-146-1.aspxOne way to implement this would be to build an INSTEAD OF INSERT Trigger or check constraint that will check for uniqueness. While you build check constraint, you might be using scalar function that would return 1 or 0 representing new record to be inserted is unique or duplicate. But that would very in effecient. Try using something else if you have some good option.~ IMWed, 11 Mar 2009 20:36:30 GMTsayfrendhttp://www.sqlservercentral.com/Forums/Topic672664-146-1.aspxI fully understand that using nocheck or ignore_dup_key clause wont help in creating a unique index/constraint. Just that i dont want to delete the existing data but want to make sure going forward all unique_recs(for a combination of col1,col2 and col3 ) is uniqueWed, 11 Mar 2009 09:06:01 GMTishaan99http://www.sqlservercentral.com/Forums/Topic672664-146-1.aspx[quote][b]ishaan99 (3/10/2009)[/b][hr]Tried adding the ignore_dup_key clause also as in index below but somehow it errors out saying The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name................any suggestions please?CREATE UNIQUE INDEX UNC_ABC ON ABC(col1,col2,col3) WITH IGNORE_DUP_KEY[/quote]What are you trying to do here?You cannot create a unique index if there are duplicate entries no matter where IGNORE_DUP_KEY is off or on.Wed, 11 Mar 2009 08:22:38 GMTmaechismo_8514http://www.sqlservercentral.com/Forums/Topic672664-146-1.aspxUnique indexes don't work that way. You were talking about a constraint. Have you tried that?Wed, 11 Mar 2009 06:56:28 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic672664-146-1.aspxTried adding the ignore_dup_key clause also as in index below but somehow it errors out saying The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name................any suggestions please?CREATE UNIQUE INDEX UNC_ABC ON ABC(col1,col2,col3) WITH IGNORE_DUP_KEYTue, 10 Mar 2009 13:29:10 GMTishaan99http://www.sqlservercentral.com/Forums/Topic672664-146-1.aspxAny reason to not just try it, on a test/dev copy of the database?I don't think it'll work, but I'm curious as to why you wouldn't just try it and find out.Tue, 10 Mar 2009 12:04:19 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic672664-146-1.aspxI have a table which has col1,col2,col3. Now if i group by on these columns there are duplicates existing ,keeping the existing data as it is, is it possible to add a unique constraint on these 3 columns. something like a constraint with nocheck clause. so that any future recs that gets added are unique.Any help on this will be greatly appreciated. TIATue, 10 Mar 2009 11:52:21 GMTishaan99