SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unique Constraint


Unique Constraint

Author
Message
ishaan99
ishaan99
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1260 Visits: 295
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



GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56879 Visits: 9730
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
ishaan99
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1260 Visits: 295
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
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56879 Visits: 9730
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
maechismo_8514
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4154 Visits: 2228
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
ishaan99
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1260 Visits: 295
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
sayfrend
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 750
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
Glenn Dorling
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3550 Visits: 926
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
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93571 Visits: 38955
The other alternative put a where clause on your inserts that checks for the existance of those three values existing in the database already.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sony Antony
Sony Antony
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 34
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search