June 16, 2011 at 11:23 am
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
June 16, 2011 at 11:30 am
Post your script.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2011 at 11:59 am
I was attempting to add this through SSMS-so I have no script
June 16, 2011 at 12:14 pm
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
June 16, 2011 at 12:26 pm
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?
June 16, 2011 at 12:33 pm
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
June 16, 2011 at 12:34 pm
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. :w00t:
June 16, 2011 at 12:52 pm
2K5 is the version
June 16, 2011 at 12:54 pm
can I use a vertically partitioned table with an existing table?
I will research this thanks
June 16, 2011 at 12:56 pm
A little more annoying... but an indexed view could do the same trick as the filtered index with pretty much the same overhead.
June 16, 2011 at 1:18 pm
Make the change, but click the "script" button in SSMS before saving. It's at the top. Then post that
June 16, 2011 at 2:50 pm
Are you creating just one Unique index on those 4 fields? Just curious because you say that it is successful on the first three columns.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply