SQL Server and index key size limit of 900 bytes

[gregdavisfromnj]

I just ran a fresh install of a process engine database of version 5.16.1 to a MS Sql Server (2012).  The create-engine script ran with the following warning…

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Warning! The maximum key length is 900 bytes. The index 'ACT_UNIQ_PROCDEF' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail.
‍‍‍‍‍‍‍‍

It appears the index in question is from this uniqueness constraint:

alter table ACT_RE_PROCDEF
    add constraint ACT_UNIQ_PROCDEF
    unique (KEY_,VERSION_, TENANT_ID_);
‍‍‍‍‍

The data types on the constraint are nvarchar(255), int, and nvarchar(255), for KEY_, VERSION_, and TENANT_ID_, respectively.  Their data storage adds up as follows:  2*255 + 4 + 2*255 = 1024.  Oops!  So, it sounds like there will be trouble if a Tenant_ID more than 193 characters long is provided to a SQL Server database.

I am tempted to "fix" the script, but I have no idea what that might break.  I don't plan on using the tenant_id, or at least, with values that long anyway.  Something to consider if you see the same error.

[trademak]

Thanks for reporting this. However the chance that this becomes an issue is quite small, because tentant ids are often not that large.

Best regards,