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 |
|
numlocked
Starting Member
2 Posts |
 Posted - 2006-05-10 : 10:59:27
|
| Hi,I have an field in my DB that is not filled in immediately (and takes a NULL value), but later has data entered into it and that data has to be unique. I'm using SQL server 2k5 and I'm wondering how to require uniqueness except in the case of a NULL value.Any help is greatly appreciated!Thanks,Chris |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-10 : 11:42:20
|
| A unique index will only allow a single null value as sql server considers two nulls to be equal in this situation (argue amongst yourselves).You can add a non-unique index and maintain uniqueness via a trigger or maybe an indexed view.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-10 : 14:18:43
|
| "or maybe an indexed view"FWIW I've done it that way before and subsequently wished I hadn't!Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-10 : 16:14:18
|
| hence the "maybe".==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-10 : 17:23:38
|
| One option might be to move the column to a new table with a one-to-one relationship to the main table.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-11 : 02:52:00
|
| "hence the "maybe""Indeed, I spotted your nuance, just wanted to reinforce it!Kristen |
|
|
|
|
|
|
Creating a unique field that allows NULLs