Unique Index

Question

Post reply

Unique Index

edwin_fredrick

Old Hand

Points: 364
More actions
December 13, 2003 at 12:28 am

#101806

I need to create Unique index on a table which has Duplicate rows. I need to prevent from adding any more duplicate rows to the table.
Thanks in Advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Well, first I would search this site for a script to remove duplicates and then add the index.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

edwin_fredrick Old Hand Points: 364 More actions · Answer 2

Duplicate data is valid data and only the key is duplicated, so i cant delete the rows the only option is to change the key values in those records to make it unique. This table have 5 years of data and has around 9 million records. Till we migrate to my new application i need to fix this.

Mean while i am look for the person who designed this database.

As you mentioned i have find to remove duplicates in master table and couple of details tables too.

Thanks for the Info.

jxflagg Hall of Fame Points: 3817 More actions · Answer 3

One option would be to use a trigger to prevent adding more duplicate entries. You could use an instead of trigger to check for existing values utilizing a non-unique index. If the value already exists, don't perform the insert or update and throw an error.

jxflagg Hall of Fame Points: 3817 More actions · Answer 4

One option would be to use a trigger to prevent adding more duplicate entries. You could use an instead of trigger to check for existing values utilizing a non-unique index. If the value already exists, don't perform the insert or update and throw an error.

edwin_fredrick Old Hand Points: 364 More actions · Answer 5

Yes !! Trigger can do the trick with some coding to check for duplicates.

Thanx jxflagg !!

Gil_Adi SSCommitted Points: 1669 More actions · Answer 6

This Is From The BOOKS ON LINE

SQL Server does not allow the creation of a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set. If attempted, SQL Server displays an error message; duplicates must be eliminated before a unique index can be created on the column(s).

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Once you have identified your dups with some script you can write a batch to update these duplicates.

Something like this will show only duplicate entries and take no further action



SELECT msgsubject

FROM mails_header

GROUP BY msgsubject

HAVING COUNT(*) > 1

Once you have eliminated duplicates I would add UNIQUE constraint rather than

using a trigger.

Hope you find the original designer. I guess this will be an interesting discussion

Frank

http://www.insidesql.de

http://www.familienzirkus.de

Edited by - Frank Kalis on 12/15/2003 01:20:16 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

edwin_fredrick Old Hand Points: 364 More actions · Answer 8

Thanks !!! Having Triggers will slow down the Application. So i choose to fix duplicate records through a small delphi application and added constraints not to allow anymore duplicates.

Frank seems to hate triggers I wonder why?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

quote:
Frank seems to hate triggers I wonder why?

Not correct!

I only have no need for them anymore.

I guess triggers have their place and time

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]