Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pug2694328
Posting Yak Master
166 Posts |
 Posted - 2007-06-13 : 10:38:49
|
| Is there a way to set a table column to only allow unique values, but allow more than one row to have a null value?I have a value that may not always be available, but I want to be sure no dupes creep in.I'd rather the table definition drive this, but know that another option is to build a proc with exclusive edit permission and error handling.CREATE or ALTER table logic would be most helpful.Thanks in advance! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-13 : 13:17:28
|
| I used the VIEW with INDEX route once. Nightmare with weird side effects with, IIRC, ARITH_ABORT and so on.Can't you just use a Trigger that rolls-back if COUNT(*) > 1 ?Kristen |
 |
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-06-13 : 14:05:02
|
| Cool, thanks s1, perfect discussion! I'm leaning toward adding a computed column to enforce the rule, using a view would require too many code changes as we'd have to flip all inserts to point at the view. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-13 : 14:08:44
|
or you name your view as is table named now and change the table name  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-06-13 : 14:22:36
|
| so clever! thx s1 :) |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-06-14 : 10:32:53
|
| Is there a good explanation as to why SQL Server 2000 defaults ARITHABORT to off? We hit an error on our table with an indexed computed column (turns out this was the most expedient way to resolve the key question of this topic based on s1's linked convo and Kristin's tragic experiences with the view option).According to the M$ doc (and my testing), I can resolve the error by setting ARITHABORT to ON. Wondering if I should do it on the connection to SQL level, or just alter the DB.Here's the error btw:'INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'' |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 10:48:31
|
| Not only have I given up with VIEWs with INDEXES on them, I've given up with Computed Columns too! Downstream we found various side effects which were a PITA to work around, and we decided to just "not use" either of these methods. Pity really, I have no idea why they can't be made to work with ArithAbort in either state.I would tackle this with a trigger (which rolls back if it find a DUPE), and an non-unique index on the column so that the trigger is nice and quick!Kristen |
|
|
|
|
|
|
|
|
unique when not null only