Note Check the solidDB Release Notes for any limitations that are associated with using a grid in the current release.
Where tables contain related data, it is more efficient if you can ensure that the related data is stored on the same grid node. Locating the child rows that are associated with a parent row would be laborious, if the child rows might be stored on any node in the grid.
You can ensure that related data is stored in the same node by specifying a matching partitioning key for related data that is stored in different tables.
The partitioning key must have the same name in all the tables that have related data and must be the following column:
▪ for the parent table, the primary key of the table
▪ for child tables, the foreign key that links the child rows with the parent row
For example, in the tables created by the following statements, corresponding rows from parent and child tables would be in the same partition:
But, in the tables created by the following statements, corresponding rows from parent and child tables would not necessarily be in the same partition:
Note If the parent table is not partitioned but the child table is partitioned, then foreign keys can be defined in the child table but foreign key checks are disabled between the non-partitioned (shared) and partitioned tables.
When records are inserted or updated in a partition, the grid only verifies that records are unique within that partition so, to ensure that child records are unique across all partitions, the primary key of a child table must include the foreign key as the first attribute.
The following example shows a parent and child table where, in both tables, the partitioning key is the value of the PKEY column, which is the primary key for the parent table and a foreign key for the child table. The primary key for the child table is set to the combination of the values in the PKEY and CNUM columns.
When the tables are partitioned the hash value of the partitioning key (shown next to each PKEY value in the following image) determines the partition and node in which each row is stored.
After the child table is partitioned, new records might be added or existing records updated. The primary key of the child table ensures that no records in a partition can be duplicated. Because new and updated records are always stored in the same partition as other records that have the same value in the PKEY field (which is the first attribute of the primary key for child records), it is not possible for duplicate child records to exist in different partitions of the same table.
