Solved

Drawback of using unique index on column

  • 11 December 2019
  • 2 replies
  • 66 views

Userlevel 1
Badge +2

Although I didn’t learn about this in my standard SF training I recently found out that there is a way in the SF to make a column unique by creating a unique index. See picture below:

 

I wonder if this is common practice when one needs a value in a column to be unique? Are there any drawbacks that I have to take into account?

I also wonder why this option isn’t available when creating the column?

icon

Best answer by Anne Buit 11 December 2019, 11:39

Hi Dennis,

There is one caveat: null-values are included when determining uniqueness. Thinkwise Platform 2018.3 and up allow you to specify that null-values should be ignored when determining the uniqueness.

You also have to keep in mind that the uniqueness is checked before any trigger logic is executed. Ensuring uniqueness in the trigger logic is not an option.

If this settings would be configured at column-level, it would be more difficult to specify that a combination of columns must contain unique values. The index allows you to select multiple columns. However, for a single unique column I'd agree that this could just as well be available at the column itself.

View original

2 replies

Userlevel 6
Badge +2

Hi Dennis,

There is one caveat: null-values are included when determining uniqueness. Thinkwise Platform 2018.3 and up allow you to specify that null-values should be ignored when determining the uniqueness.

You also have to keep in mind that the uniqueness is checked before any trigger logic is executed. Ensuring uniqueness in the trigger logic is not an option.

If this settings would be configured at column-level, it would be more difficult to specify that a combination of columns must contain unique values. The index allows you to select multiple columns. However, for a single unique column I'd agree that this could just as well be available at the column itself.

Userlevel 1
Badge +2

Thanks for these details! And the prompt answer :thumbsup_tone1: .

Reply