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»»

How to set a unique key on a bit field Expand / Collapse
Author
Message
Posted Thursday, June 16, 2011 11:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 9, 2011 3:09 PM
Points: 10, Visits: 37
I have a table that I would like to have a unique key based on 4 fields.The first 3 will pass with no problems but the last field is a bit field. when I try to add the key It wont allow me I am assuming because the bit field is ('false') and there are multiple that match that.
What I am trying to do is to keep them from adding multiple records with the bit field set to ('true') along with the other keys.
Such as:
BureauID+CompanyID+DivisionID+DefaultType(where this is true)

Thanks
Post #1126794
Posted Thursday, June 16, 2011 11:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 42,846, Visits: 35,975
Post your script.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1126801
Posted Thursday, June 16, 2011 11:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 9, 2011 3:09 PM
Points: 10, Visits: 37
I was attempting to add this through SSMS-so I have no script
Post #1126820
Posted Thursday, June 16, 2011 12:14 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
It's the kind of thing you can either do through partitioning, or through moderately complex triggers, or through indirect means in the data design.

In your case, a vertically partitioned table might be the best bet. You can look those up online pretty easily.


- 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 #1126829
Posted Thursday, June 16, 2011 12:26 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
Are you on 2k5 or 2k8? I ask because poeple often post in the wrong forum.


Assuming 2K8...

Wouldn't it be possible to create a unique filtered index to avoid the pain of the triggers?
Post #1126838
Posted Thursday, June 16, 2011 12:33 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Ninja's_RGR'us (6/16/2011)
Are you on 2k5 or 2k8? I ask because poeple often post in the wrong forum.


Assuming 2K8...

Wouldn't it be possible to create a unique filtered index to avoid the pain of the triggers?


Yep. That's another way to do it. Thought of that, but assumed that 2k5 forum was picked for a reason.


- 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 #1126849
Posted Thursday, June 16, 2011 12:34 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
GSquared (6/16/2011)
Ninja's_RGR'us (6/16/2011)
Are you on 2k5 or 2k8? I ask because poeple often post in the wrong forum.


Assuming 2K8...

Wouldn't it be possible to create a unique filtered index to avoid the pain of the triggers?


Yep. That's another way to do it. Thought of that, but assumed that 2k5 forum was picked for a reason.




Agreed... but I've seen something like 4-5 "wrong version" posts today so I just figured I might talk about it . Not to mention that I don't always post the version in google so all in all an ok call in my book.
Post #1126850
Posted Thursday, June 16, 2011 12:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 9, 2011 3:09 PM
Points: 10, Visits: 37
2K5 is the version
Post #1126871
Posted Thursday, June 16, 2011 12:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 9, 2011 3:09 PM
Points: 10, Visits: 37
can I use a vertically partitioned table with an existing table?
I will research this thanks
Post #1126878
Posted Thursday, June 16, 2011 12:56 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
A little more annoying... but an indexed view could do the same trick as the filtered index with pretty much the same overhead.
Post #1126881
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse