Foreign and unique key limitations in partitioned tables
Note Check the solidDB Release Notes for any limitations that are associated with using a grid in the current release.
Because the rows of a partitioned table are located on different nodes in a grid, the use of foreign and unique keys is limited to ensure that operations that check the uniqueness and validity of key values can take place on a single node.
The following restrictions apply:
▪ Where a foreign key is used to link parent and child tables, the following conditions must be true:
– In the child table, the foreign key must be the first attribute of the primary key for the table (and therefore the attribute that is used as the partitioning key).
– The name of the foreign key column in the child table must be the same as the first (or only) attribute of the primary key of the parent table (so that the partitioning key for each table has the same name).
▪ You cannot create uniqueness constraints (for example, use the UNIQUE keyword or create a UNIQUE index) on any other columns in the table (other than the primary key). If you attempt to create a schema with these constraints, the following error message is returned:
SOLID Table Error 13625: Unique key definition does not match PARTITION BY definition (colname: colname)
However, you can perform the following tasks with partitioned tables:
▪ Use multiple columns to create a composite primary or foreign key.
▪ Use ON DELETE CASCADE with the FOREIGN KEY clause (when you create a partitioned table) in order to automatically delete the associated child rows when the corresponding row in the parent table is deleted.