Unique Constraint

  • I 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. TIA

  • Any 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.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • 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

  • Unique indexes don't work that way. You were talking about a constraint. Have you tried that?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • ishaan99 (3/10/2009)


    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

    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.

  • I 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 unique

  • One 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.

    ~ IM

  • I 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.

  • The other alternative put a where clause on your inserts that checks for the existance of those three values existing in the database already.

  • I 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 UNIQUE

    Regards,

    Sony Antony.

  • Another 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.

    Susantha

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

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