Table Hierarchies

The Oracle NoSQL Database enables tables to exist in a parent-child relationship. This is known as table hierarchies.

The create table statement allows for a table to be created as a child of another table, which then becomes the parent of the new table. This is done by using a composite name (a name_path) for the child table. A composite name consists of a number N (N > 1) of identifiers separated by dots. The last identifier is the local name of the child table and the first N-1 identifiers are the name of the parent.

Semantics

The semantic implications of a parent-child relationship are the following:

A child table inherits the primary key columns of its parent table. This is done implicitly, without including the parent columns in the create table statement of the child. For example, in the following Example 5-8 example, table A.B has an extra column, called ida, and its primary key columns are ida and idb. Similarly, table A.B.C has 2 extra columns, ida and idb, and its primary key columns are ida, idb, and idc. The inherited columns are placed first in the schema of a child table.
All tables in the hierarchy have the same shard key columns, which are specified in the create table statement of the root table. So, in our example, the common shard key is column ida. Trying to include a shard key clause in the create table statement of a non-root table will raise an error.
A parent table cannot be dropped before its children are dropped.
When two rows RC and RP from a child table C and its parent table P, respectively, have the same values on their common primary key columns, we say that RP and RC match, or that RP contains RC. In this case, RP and RC will also be co-located physically, because they have the same shard key. Given that a child table always has more primary key columns than its parent, a parent row may contain multiple child rows, but a child row will match with at most one parent row.

Note:

Oracle NoSQL Database does not require that all the rows in a child table have a matching row in the parent table. In other words, a referential integrity constraint is not enforced.

Given that the Oracle NoSQL Database model includes arrays and maps, one may wonder why are child tables needed? After all, for each parent row, its matching child rows could be stored in the parent row itself inside an array or map. However, doing so could lead to very large parent rows, resulting in bad performance. This is especially true given the append-only architecture of the Oracle NoSQL Database store, which implies that a new version of the whole row is created every time the row is updated. So, child tables should be considered when each parent row contains a lot of child rows and/or the child rows are large. If, in addition, the child rows are not accessed very often or if they are updated very frequently, using child tables becomes even more appealing.

Example 5-8 Table Hierarchy

The following statements create a table hierarchy, that is a tree of tables connected by parent-child relationships. A is the root table, A.B and A.G are children of A, and A.B.C is a child of A.B (and a grandchild of A).

CREATE TABLE A (
       ida INTEGER, a1 STRING, a2 INTEGER, PRIMARY KEY(ida));
CREATE TABLE A.B (
        idb INTEGER, b1 STRING, a2 STRING, PRIMARY KEY(idb));
CREATE TABLE A.B.C (
        idc INTEGER, b1 STRING, c2 STRING, PRIMARY KEY(idc));
CREATE TABLE A.G (
        idg INTEGER, g1 STRING, g2 DOUBLE, PRIMARY KEY(idg));

Table Hierarchy in a Multi-Region table:

You can create child tables in an existing Multi-Region architecture. For example, create the table users in two regions, FRA and LON.

CREATE TABLE users (
     id INTEGER,
     name STRING,
     team STRING,
     PRIMARY KEY (id))
   IN REGIONS FRA,LON;

Under the users table, you can create a child table using this statement.

CREATE TABLE users.userdet (
       pan INTEGER,
       address STRING,
       email STRING,
       PRIMARY KEY(pan));

Specifying the REGIONS clause while creating a Multi-Region child table will result in an error as illustrated below.

REATE TABLE users.userinfo (pan INTEGER, address STRING, email STRING,  PRIMARY KEY(pan)
    IN REGIONS FRA,LON) ;

Error handling command CREATE TABLE users.userinfo (
       pan INTEGER,
       address STRING,
       email STRING,
       PRIMARY KEY(pan) IN REGIONS FRA,LON): Error: at (5, 24) missing ')' at 'IN', at line 5:24
       rule stack: [parse, statement, create_table_statement]

You can view the description of the Multi-Region child table as shown below. Note that the child table automatically inherits the primary key columns of its parent table.

sql-> desc as json table users.userdet;
{
  "json_version" : 1,
  "type" : "table",
  "name" : "userdet",
  "parent" : "users",
  "regions" : {
    "2" : "FRA",
    "1" : "LON"
  },
  "fields" : [{
    "name" : "id",
    "type" : "INTEGER",
    "nullable" : false
  }, {
    "name" : "pan",
    "type" : "INTEGER",
    "nullable" : false
  }, {
    "name" : "address",
    "type" : "STRING",
    "nullable" : true
  }, {
    "name" : "email",
    "type" : "STRING",
    "nullable" : true
  }],
  "primaryKey" : ["id", "pan"],
  "shardKey" : ["id"]
}