Prevent Duplicates in a field

  • Thread starter Thread starter Steve Roberts
  • Start date Start date
S

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
 
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
 
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.
 
Back
Top