unique="true" shows warning "No database indexes defined for unique attributes".

[Former Member]

When I add unique="true" to an attribute modifier on items.xml, I got the following warning from Hybris plugin on Intellij: "No database indexes defined for unique attributes".

Should I define an index for all my unique attributes?

Thanks!

[arvind-kumar_avinash · Accepted solution]

For performanace reason, you should always add an index for unique attributes of an itemtype but it is not mandatory. Please look at the following code:

 <itemtype generate="true" code="MyTestItem" jaloclass="org.training.jalo.TestItem" autocreate="true">
     <deployment table="MyTestItem" typecode="29000"/>
     <attributes>
         <attribute qualifier="myExampleStringField" type="java.lang.String">
             <modifiers unique="true"/>
             <persistence type="property"/>
         </attribute>
     </attributes>
 </itemtype>

It will compile successfully and you can use this itemtype for all practical purposes but you may incur a big performance penalty.

A unique modifier creates a unique constraint. In all databases (e.g. Oracle, SQL Server), when a unique constraint is created a corresponding unique index is automatically created on the column(s). However, a unique constraint is not guaranteed to create a new index, nor is the index it creates guaranteed to be a unique index. Therefore, if you desire a unique index to be created for query performance issues, you should explicitly create one.

I would like to end this answer with the following notes from https://help.hybris.com/1811/hcd/8ecae959b9bd46b8b426fa8dbde5cac4.html :

There should be a covering index defined that includes all the unique attributes for type. There are Service Layer interceptors that validate uniqueness for each type, which generate a query for all the unique attributes. Failure to add an index can cause serious performance issues. Furthermore, the validation of the unique attributes in the Service Layer cannot guarantee that there won't be duplicate records in the database, only a unique index/constraint can do this. The following displays a non-compliant example:

 <itemtype code="MyType" extends="GenericItem"
      autocreate="true" generate="true">
      <deployment table="MyType" typecode="XXXX"/>
      <attributes>
          <attribute qualifier="uid" generate="true" autocreate="true" type="java.lang.String">
              <persistence type="property"/>
              <modifiers optional="false" unique="true"/>
          </attribute>
      </attributes>
      <indexes>
      <!-- violation here because missing index for uid -->
      </indexes>
  </itemtype>

[arvind-kumar_avinash · Accepted solution]

Hi @JDC - did the updated answer provide you with the information that you needed?

[Former Member · Accepted solution]

Yes, Arvind. Thank you very much.

[Former Member]

Thanks Arvind.

But I would like to know the difference between a unique field with and without a declared index.

[arvind-kumar_avinash]

I have updated my answer (added one more paragraph at the end) to make it a bit more clear.

[Former Member]

Thanks, Arvind.

[arvind-kumar_avinash]

You are most welcome.