Automatically create unique index for lookup column

For us, a unique index on these columns isn’t always the answer. Often we create a new calculated column to use for the lookup. In very specific situations we even accept that the lookup might have double values. The validation forces us to think about the best solution.

Automatically adding the unique constraints could lead to some unexpected changes. The idea could still work but I’d like to be notified/warned when a unique constraint is automatically added and I’d like to be able to change/remove it if needed.

I agree with your comment, @J. de Lange. It should definitely based on a setting per lookup. The default behaviour should be true (maybe a project setting). For existing projects however, this setting should definitely be turned off and make it a developers decision if it is to be turned on.

Too bad I can't edit my comment, so here we go…

Your situation, using a calculated column as a lookup could be resolved/work in the same way. Try this example:

--create a table with a computed column
create table cities (id          int primary key
                    ,name        varchar(100)
                    ,postal_code varchar(100)
                    ,name_postal_code as name + ' ' + postal_code --computed column based on name and postal code
                    )

--create unique index based on the computed column
create unique index cities_unique_lookup on cities (name_postal_code)
go

--insert a value
insert into cities (id
                   ,name
                   ,postal_code
                   ) 
values (1
       ,'apeldoorn'
       ,'1234ab'
       )

--insert second value, this one should fail since name_postal_code must be unique 
insert into cities (id
                   ,name
                   ,postal_code
                   ) 
values (2
       ,'apeldoorn'
       ,'1234ab'
       )
go

--cleanup
drop table cities
go

Hi Robert Jan,

There are reasons for developers to approve this validation. For instance if the column isn't used for any lookup field but only shown in the breadcrumb. The automatic creation of an Unique index would then be unnecessary. It would also be counter-productive if later the display/lookup column gets changed - then an new index would be created and so on.

What we certainly can do is expand a bit in the Validation Solution field to explain that the validation can be approved if for instance the lookup column/display column is not used inside a lookup, and is only there to be shown in the breadcrumb. Introducing a project version setting for this is also not the way of solving this we think. Do you agree @Robert Jan de Nie?

I got you one better! Why cant we specify per lookup (with a simple checkbox) if that lookup should be unique. If so, the SF will create an index for us. This would give you more control and you wont have to specify an index for each lookup that needs to be unique (most of them).