Post reply

Dup values preventing me from creating a UNIQUE INDEX OR CONSTRAINT

  • June 22, 2011 at 9:53 am

    #240256

    Hi Everybody,

    I'm trying to create either a UNIQUE INDEX or UNIQUE CONSTRAINT on three fields in a table and I cannot due to duplicate values that are in the table. There are many records with dups actually. I have found some documentation on BOL that indicates that you cannot create either one of these while dup values exist. Does anybody have a work around or know of an efficient way to achieve this? A developer told me to use a trigger, but I wanted to check with the guru's first.

    Thanks to whom answers this question in advance.

    David 🙂

  • June 22, 2011 at 9:56 am

    #1342541

    Well, no, because that would be a violation of the definition of UNIQUE.

    What you want to do is either clean out the duplicate values or re-define your index/constraint.

    The big question is, what are you trying to accomplish with creating this index? Better performance? Guarantee of no future dupe values? SOmething else?

  • June 22, 2011 at 10:18 am

    #1342566

    Hi Pam,

    Yes, I understand the purpose for creating the UNIQUE INDEX or UNIQUE CONSTRAINT is for uniqueness. We're trying to prevent dup values in the table moving forward that's why we're trying to do this.

    Thanks,

    David

  • June 22, 2011 at 10:23 am

    #1342572

    Do you need to keep the old dupe values?

    If so, about your only solution is to create an on update / insert trigger and manually check.

    If not, clean up the data then create your unique index.

    If you need to keep the old duped data but it doesn't need to be readily accessible you could always archive it off to a separate table.

  • June 22, 2011 at 10:38 am

    #1342587

    We haven't eliminated the option of deleting the dup values, but the idea of using a trigger seems probable.

    Thanks

  • June 22, 2011 at 10:39 am

    #1342588

    Just be aware that triggers can seriously degrade performance so if you can get rid of the dupes that would be best.

  • June 22, 2011 at 10:41 am

    #1342592

    Will do. Thanks for your input and many thanks!!!

  • June 22, 2011 at 10:47 am

    #1342596

    If the opportunity presents itself to upgrade to 2k8, you could do a filtered index that would let you do this only with newer data if you have an incrementing ID or a timestamp on the column allowing for you to apply it forward.

    In 2k5 and back... yeah, you're out of luck. Be aware that your update trigger is going to choke on the older records if they update a different column and don't force the column in question to a new unique status.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 8 posts - 1 through 7 (of 7 total)

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