Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Unique Constraint Expand / Collapse
Author
Message
Posted Tuesday, March 10, 2009 11:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 19, 2013 11:03 AM
Points: 374, Visits: 280
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


Post #672664
Posted Tuesday, March 10, 2009 12:04 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #672674
Posted Tuesday, March 10, 2009 1:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 19, 2013 11:03 AM
Points: 374, Visits: 280
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



Post #672734
Posted Wednesday, March 11, 2009 6:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #673200
Posted Wednesday, March 11, 2009 8:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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.


Post #673300
Posted Wednesday, March 11, 2009 9:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 19, 2013 11:03 AM
Points: 374, Visits: 280
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


Post #673358
Posted Wednesday, March 11, 2009 8:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:42 PM
Points: 89, Visits: 748
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
Post #673895
Posted Wednesday, March 11, 2009 9:15 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 7, 2014 6:44 PM
Points: 1,993, Visits: 864
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.
Post #673907
Posted Wednesday, March 11, 2009 9:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:03 PM
Points: 23,045, Visits: 31,569
The other alternative put a where clause on your inserts that checks for the existance of those three values existing in the database already.




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)
Post #673913
Posted Saturday, March 14, 2009 1:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 9:27 AM
Points: 322, 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.
Post #675807
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse