Preventing non-null duplicates
-
Hello - I would like to prevent users from adding duplicate values to a field which also contains many null values. I've spoken to two developers that tell me that I have to create a composite index. However, on closer examination, we found that we can't create a composite index that doesn't include any duplicates. Could someone please point me in the right direction (I've got a grand total of two days working with SQL Server). Thank-you!
Tom Folinsbee
-
Eventhough creatting a trigger for that is not a difficult thing if you are allowed to use all the capabilities of sql server 2000 you have another choice: Indexed views
let me be clear so that there is no doubt:
create a view that select that field from the table like:
Create view dbo.vw_test as
select MyNullableField From MyTable where MyNullableField is not Null
and then :
create unique clustered index ix_vew on vw_test(MyNullableField 
Now if you try to save a duplicate there you will get an error therefore you are protected with the index
for a version with the trigger you can take a look at This MS Tip
HTH
* Noel -
First of all I would do the following (Using of course your table and column names)
Update dbo.customers set Region = 'unknown'
where (Region) IS NULL -- repeat for each column which could contain nulls and if column is numeric substitue a numeric value for 'unknown'
Then to determine the scope of the duplicate problem run the following query.
Select contacttitle, region, count(region)
from dbo.customers
group by contacttitle, region
having count(region) > 1
ORDER BY COUNT(region) DESC
This should give you an idea of the magnitude of your problem. Remember if you try to delete duplicates do NOT delete the last one !!! -- Seach here there are lots of samples of procedures to delete duplicate entries.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply