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 |
|
waveform
Yak Posting Veteran
93 Posts |
 Posted - 2011-07-25 : 04:14:39
|
| Hi, I was just reading this thread regarding multiple NULLS in a "unique" column:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48614My problem is similar, though using a varchar which could have multiple blanks (I don't like nulls :D). It's a "job" system where jobs are added and assigned tasks; multiple tasks per job. The JobNumber field is a varchar, and of course must be unique, but jobs won't always have a job number when they're created. Many of them will be blank at first and filled in later.What is the best way of implementing this? Should I just not make the index unique and handle dup checking in code at time of update? Or is there a "best practice" way of handling this kind of thing purely in SQL?Many thanks! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-25 : 10:21:51
|
| You could use a check constraint or a trigger.By the way, inserting empty strings instead of NULLs doesn't reallly solve any problems, instead, it introduces some. |
 |
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2011-07-25 : 21:47:10
|
| Not sure what problems you mean. If you have a field storing say "Address Line 2" and there isn't an address line 2, what else do you put in it besides ''? 'This Field Intentionally Left Blank'? :) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-25 : 23:07:21
|
Leave it NULL.  Not liking NULLs isn't a very good reason to put in fake values. |
|
|
|
|
|
|
Unique value but allow multiple blanks