Solved

Create unique constraint

  • 10 September 2020
  • 4 replies
  • 54 views

Badge +1

To ensure that no duplicate values are entered in specific columns that do not participate in the primary key, you can create a unique constraint. A unique constraint automatically creates a corresponding unique index.

 

Does the SF also give this option?

icon

Best answer by Anne Buit 10 September 2020, 14:42

Hi Robert,

This is currently not possible. We always enforce a uniqueness rule by creating an unique index. Both implementation options within the database are virtually the same - the user may not provide duplicate values and an index is created to quickly verify uniqueness. 

To my knowledge, there are no functional or technical benefits to allow the Software Factory to transform the uniqueness rule to a unique constraint over of a unique index.

Vice-versa, a benefit of the unique index is that it allows us to apply a filtering (exclude null values).

Translations for the messages shown when failing to meet the unique index requirements are provided by the platform. A unique constraint would cause different messages to be thrown by the database, requiring maintenance on additional translations as well.

However, I do see that it might be better to allow developers to define the uniqueness rules in the model at constraints or perhaps a completely separate segment, rather than with the ‘regular’ indexes (regardless of how this is transformed into database objects).

Is there a reason for using unique constraints over unique indexes that I’m overlooking or is it more a matter of preference?

View original

4 replies

Userlevel 7
Badge +14

Hi Robert,

The SF does indeed give developers the option to create Unique indexes. In Datamodel → Tables → Indexes you can create them by checking the Unique checkbox and by naming the index columns.

 

The combination of the index columns then are Unique. Hope this answers your question!

Kind regards,
Mark Jongeling

Badge +1

Hi Mark,

 

I have tried this option.

However, in my opinion, in this way you only create a unique index. A unique index ensures that the values in the index key are unique.

 

I would like to create a unique constrain. A unique constraint guarantees that no duplicate values can be entered in the column(s) on which the constraint is created. When a unique constraint is created a corresponding unique index is automatically created on the column(s).

 

Does the SF also give this option?

 

Kind regards,

Robert Egberts

Userlevel 6
Badge +4

Hi Robert,

This is currently not possible. We always enforce a uniqueness rule by creating an unique index. Both implementation options within the database are virtually the same - the user may not provide duplicate values and an index is created to quickly verify uniqueness. 

To my knowledge, there are no functional or technical benefits to allow the Software Factory to transform the uniqueness rule to a unique constraint over of a unique index.

Vice-versa, a benefit of the unique index is that it allows us to apply a filtering (exclude null values).

Translations for the messages shown when failing to meet the unique index requirements are provided by the platform. A unique constraint would cause different messages to be thrown by the database, requiring maintenance on additional translations as well.

However, I do see that it might be better to allow developers to define the uniqueness rules in the model at constraints or perhaps a completely separate segment, rather than with the ‘regular’ indexes (regardless of how this is transformed into database objects).

Is there a reason for using unique constraints over unique indexes that I’m overlooking or is it more a matter of preference?

Badge +1

Hi Anne,

 

I was assuming that a unique constraint would prevent duplicate values from being entered. But as you indicate, the unique index takes care of this functionally. Then I see no reason to impose a unique constraint first. I'm going to roll out the unique index and test it on the database.

 

Thanks for your comment,

Robert Egberts

Reply