SQL Server Performance Forum – Threads Archive

Unique Index

I want to create an Unique Index on a field that allows null. In this uniqueness I don’t want to consider the null. How can I do it ?

Not sure if I understand you correctly, but you can’t say:
Index all rows, but not those that contain NULLs.
One and only one row can contain a NULL. Any further NULL would violate the contraint Frank
http://www.insidesql.de
http://www.familienzirkus.de

You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Frank, I think you understood my problem, but In Access we can create the uniques indexes without considering Null. For Ex. A field ‘Phone’ and it contains 359084
947235
449534
<Null>
805047
<Null> If I try to create a unique index on this field ‘Phone’, SQL Server will not allow, but we can do it Access. So, I need the help in how we can make it possible in SQL Server ?

As referred its not possible in SQL! You can attach unique constraints to columns that allow null values, whereas you can attach primary key constraints only to columns that do not allow null values. When you attach a unique constraint to a column allowing null values, you ensure that at most one row will have a null value in the constrained column.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

No chance for you! Access exposure can be treated [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Must it be UNIQUE at all? Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

I am sorry, I am not satisfied with these answers, because it is possible in MS Access. My first experience is in Access. Ok, Any other solution ( any other method to make it possible )

Nothing you really want to hear.
AFAIK, you won’t find a major RDBMS (at least not Oracle, DB/2 and SQL Server) that allows this. Oracle does not even allow to place such a constraint on a nullable column. However, you could:
– correct the data
– write your app to check this before actually inserting the data What do you want this for? Frank
http://www.insidesql.de
http://www.familienzirkus.de

Take it easy guys.
How about some string with simulate null. I mean something you can indentify as null in your searchs? Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

I won’t do this. NULL is not an empty string or a blank. That’s a totally different logic. You’re only building up problems! Frank
http://www.insidesql.de
http://www.familienzirkus.de

Hey, I have been promoted to a five-star member !!! Watch out, those who have more posts than me. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

The only thing I could suggest is to split the table vertically, so that one table has all columns except the column that you want to index, and the other table has the primary key plus this column. create your index as required Then create a view which outer joins them back together This will require more work to insert/update/delete, but not that big a deal… and it could be totally transparent to the application Cheers
Twan

I would rethink my logic. What happens when two persons have no telefone? (unlikely nowadays, but possible)? I would put an index on that column and that’s it. Just my $0.02 Frank
http://www.insidesql.de
http://www.familienzirkus.de

quote:Originally posted by FrankKalis Nothing you really want to hear.
AFAIK, you won’t find a major RDBMS (at least not Oracle, DB/2 and SQL Server) that allows this. Oracle does not even allow to place such a constraint on a nullable column. However, you could:
– correct the data
– write your app to check this before actually inserting the data What do you want this for? Frank
http://www.insidesql.de
http://www.familienzirkus.de

FYI this is not correct
SQL Server allows only one row to contain the value NULL for the complete unique key (single or multiple column index), while Oracle allows any number of rows to contain the value NULL for the complete unique key

]]>