Unique Constraint

Question

Post reply

Unique Constraint

ishaan99

SSCertifiable

Points: 6261
More actions
March 10, 2009 at 11:52 am

#128948

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

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

ishaan99 SSCertifiable Points: 6261 More actions · Answer 2

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

GSquared SSC Guru Points: 260824 More actions · Answer 3

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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

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.

ishaan99 SSCertifiable Points: 6261 More actions · Answer 5

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

sayfrend SSCommitted Points: 1733 More actions · Answer 6

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

Glenn Dorling SSCertifiable Points: 7878 More actions · Answer 7

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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

Sony Antony SSC-Addicted Points: 452 More actions · Answer 9

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.

Susantha Bathige Ten Centuries Points: 1040 More actions · Answer 10

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