1

I have 2 fields in my table, IDProvider and reference. The table is this:

ID
MyOwnReference
IDProvider
Reference

I would like that if IDProvider and Reference are both not null, the the constraint should to check if there are duplicates, because a provider can't have duplicate references. However, I would like to avoid the check if at least one of them are null. because I can know the provider but not the reference, or I can know the reference but not the provider (I accept that this can be a bit odd case, if I know the reference, I have to know the provider). But anyway, I would like to handle this posibility.

I have tried to create a unique index, but when I try to add a second record with the IDProvider and Reference at null, I get an error that the value is duplicate because exists one index with null, null values.

I am using Sql Server 2017 express.

Another option it would to have a [Unkown] provider and assign to him a dummy reference, but this makes me to add more logic to my application to determinate a reference that it is not duplicate.

Anyway I can accept ideas because I could change this behavior, it is not a problem, if really an index with null values it is a very bad idea.

Thanks.

Álvaro García
  • 18,114
  • 30
  • 102
  • 193

1 Answers1

9

You can create unique filtered non-clustered index on these two fields, which includes only those records, where both fields are filled:

create unique nonclustered index IX_UNIQUE_FILTERED on dbo.MyTable(IDProvider, Reference)
where IDProvider is not null and Reference is not null
Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32