nullable and unique column

I created a table and one of its column can be null, or
unique value.
it turned out that I could only insert one null record for
this column,
I had 'duplicate key...' error when trying to insert the
second null for this column.
what is the way to make a column that is nullable for more
than one record, and
is unique if the data is not null?
thanks!One way to achieve that is to create a view that selects only the
non-NULL rows and create a unique clustered index on the relevant
columns.
HTH,
Gert-Jan
amy wrote:
> I created a table and one of its column can be null, or
> unique value.
> it turned out that I could only insert one null record for
> this column,
> I had 'duplicate key...' error when trying to insert the
> second null for this column.
> what is the way to make a column that is nullable for more
> than one record, and
> is unique if the data is not null?
> thanks!|||Hi Amy,
Thank you for using MSDN Newsgroup! My name is Billy and it's my pleasure to assist you
with your issue.
From your description, I understand that you would like to allow multiple nulls in a UNIQUE
column. Have I fully understood you? If there is anything I misunderstood, please feel free to
let me know.
Unfortunately, this feature is not allowed in relational databases that adhere to the SQL
standard. As we know, Null, by definition is an "unknown" value and is not equal to any other
value even the null itself.
However, UNIQUE constraint regards Null as other normal values. In this way, a unique
column only allow one Null. On the other hand, for the purposes of unique indexes, SQL Server
also treats all NULLs as equal so you cannot have more than one.
It's better to not allow Null in a unique column as SQL Server allows only one null (I understand
this may be a problem when you insert rows and left that column null/blank). If you need to
perform like that, I recommend you use some check mechanism such as triggers to check
rows inserted into the table first to avoid the duplicated nulls errors.
Amy, does this answer your question? If there is anything more I can do to assist you, please
feel free to post it in the group
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you Billy Yao !
Yes, now I'm using a trigger to check the data during
insert.
-amy
>--Original Message--
>Hi Amy,
>Thank you for using MSDN Newsgroup! My name is Billy and
it's my pleasure to assist you
>with your issue.
>From your description, I understand that you would like
to allow multiple nulls in a UNIQUE
>column. Have I fully understood you? If there is anything
I misunderstood, please feel free to
>let me know.
>Unfortunately, this feature is not allowed in relational
databases that adhere to the SQL
>standard. As we know, Null, by definition is an "unknown"
value and is not equal to any other
>value even the null itself.
>However, UNIQUE constraint regards Null as other normal
values. In this way, a unique
>column only allow one Null. On the other hand, for the
purposes of unique indexes, SQL Server
>also treats all NULLs as equal so you cannot have more
than one.
>It's better to not allow Null in a unique column as SQL
Server allows only one null (I understand
>this may be a problem when you insert rows and left that
column null/blank). If you need to
>perform like that, I recommend you use some check
mechanism such as triggers to check
>rows inserted into the table first to avoid the
duplicated nulls errors.
>Amy, does this answer your question? If there is anything
more I can do to assist you, please
>feel free to post it in the group
>Best regards,
>Billy Yao
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>.
>