Tenant group tables and shared tables can be useful, but using them with sequences requires careful consideration. The challenge comes from the fact that shared tables and tables with tenant groups are shared by some or all tenants of the database, where as multi-tenant sequences have no groups and are never shared, but are always separately owned by each regular tenant in the database. This means that for each multi-tenant sequence that you define, every tenant that executes a sequence function generates and returns the same set of values. The tenant can then assign these values to a group or shared table, but with certain restrictions:
If the field requires a unique value, as for a primary index, the AVM will raise an error when any tenant tries to assign an existing value previously assigned by another tenant.
If the field represents a foreign key, queries on that foreign key with the same value will return multiple records from different tenants. However, information about which tenant owns which record is lost within the group or shared index on that foreign key.
Clearly, the only solution to assign sequence values to a primary key is to use a shared sequence. The best practice to avoid record ambiguity with foreign keys in group or shared tables is to always use shared sequences to assign values to foreign keys in these types of tables.
If you plan to include multiple tables with groups in a join, you might want to ensure that the groups in each table have the same tenant membership.
Groups also have identity, similar to tenants, using a name or integer ID. You might chose to implement separate shared sequences to assign key values for each group in a table. In the CREATE trigger, where you would typically assign the key values to a record, you can test the group identity for the record in order to determine which sequence to use to assign a key value. You can return this group identity using the following ABL elements:
BUFFER-GROUP-NAME and BUFFER-GROUP-ID function — Returns the tenant group identity that owns the current record in the specified buffer.
BUFFER-GROUP-NAME and BUFFER-GROUP-ID attribute on a buffer handle — Returns the tenant group identity that owns the current record referenced by the specified buffer handle.
If the field requires a unique value, as for a primary index, the AVM will raise an error when any tenant tries to assign an existing value previously assigned by another tenant.