Using multi-tenant sequences with shared and group-configured tables
Shared and tables with groups can be useful, but using them with sequences requires some care. The challenge has to do with 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 the same set of values. The tenant can then assign these values to a shared or group-configured table, but with the following problems:
If the field requires a unique value, as for a primary index, the AVM raises 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 can 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.
As a result, to use sequences effectively with shared and group-configured tables requires certain restrictions. The following example demonstrates some of the problems with sequence interactions with shared and group-configured tables.
Suppose the Customer table in a database is multi-tenant-enabled and does not participate in any group. The Customer.cust-num key is generated by a sequence that has been made incorrectly multi-tenant. The Order table also contains cust-num as a foreign key and it participates in a group with tenants 1 to 3. A Customer record is created by tenants 1, 2, and 3. All three Customer records get the cust-num value 1 because each tenant has its own instance of the multi-tenant sequence, as well as its own instance of the Customer table. However, as tenants 1, 2, and 3 create orders for customer 1, you cannot identify the tenant that owns each Order record written for customer 1, where Order.cust-num = 1, because all three tenants in the group share the same Order table instance.
When you run the following query for tenant 2, it displays the orders for customer 1 from all three tenants that share the Order table:
FOR EACH Customer, EACH Order OF Customer:
DISPLAY Order.cust-num Order.order-num.
END.
Because there is only one index in this group table containing cust-num and order-num for the entire group of orders, all orders for all Customer records with cust-num = 1 are treated the same for all three tenants.
If the field requires a unique value, as for a primary index, the AVM raises an error when any tenant tries to assign an existing value previously assigned by another tenant.