Prevent Duplicates in a field

[Steve Roberts]

I created a table that contains an EmployeeID PCAssetNumber and DisplayAsset
Number. I have the constrains for PCAssetNumber and DisplayAssetNumber Set
to No duplicates but each allows nulls. At the request of the data-entry
person I created a datagrid form. The problem happend once there is a null
entry in either of the no diplicate fields. Once the is a null and I go to
add the next row it gives me a warning that there is a duplicate(because I
haven't entered a value in the Asset fields yet. Is there a way to have it
both ways? No duplicate values but ignore nulls?

Thanks

Steve

 

[Vadim Rapp]

Hello Steve:
You wrote on Thu, 16 Jun 2005 10:56:48 -0600:

SR> Is there a way to have it both ways? No duplicate values but ignore
SR> nulls?

No. From Books Online:

Creating a Unique Index

....
Note 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.

Vadim Rapp

 

[Sylvain Lafontaine]

Your only possibility is to use a trigger that will abort the transaction if
the inserted or updated value is already in the table.

I'm sorry that I cant give you an example right now as it have been too long
since the last time that I wrote a trigger but you can ask in the
m.p.sqlserver.programming newsgroup if you need one.

 

[Razvan Socol]

Your only possibility is to use a trigger that will abort the transaction if
the inserted or updated value is already in the table.

There are also other posibilities:
1. a unique constraint over a view
2. a unique constraint on a computed column.
See:
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/2e46db070564e519

Razvan