How can one interleave with two tables?

Question

Let's pretend I have the schema

CREATE TABLE Account (
AccountId BYTES(MAX),
Foo STRING(1024)
) PRIMARY KEY (AccountId);"

CREATE TABLE Customer (
CustomerId BYTES(MAX),
Bar STRING(1024)
) PRIMARY KEY (CustomerId);"

And I create a new table:

CREATE TABLE Order (
AccountId BYTES(MAX),
CustomerId BYTES(MAX),
Baz STRING(1024)
) PRIMARY KEY (AccountId, CustomerId);"

That I'd like to INTERLEAVE with Account and Customer. How can one do this? I'm familiar with how to INTERLEAVE with one table, when building a hierarchy, but not sure how to achieve this with two tables.

I have found [this](https://cloud.google.com/spanner/docs/schema-and-data-model#creating_a_hierarchy_of_interleaved_tables) documentation that could be helpful for you. — ericcco, Sep 17 '19 at 15:49

score 6 · Accepted Answer · answered Sep 17 '19 at 15:48

You cannot interleave one table in two other tables, but you can create a hierarchy of interleaved tables. In your example, that would mean interleaving the Customer table in the Account table, and the Order table in the Customer table like this:

CREATE TABLE Account (
  AccountId BYTES(MAX),
  Foo STRING(1024)
) PRIMARY KEY (AccountId);

CREATE TABLE Customer (
  AccountId BYTES(MAX),
  CustomerId BYTES(MAX),
  Bar STRING(1024)
) PRIMARY KEY (AccountId, CustomerId),
INTERLEAVE IN PARENT Account;

CREATE TABLE Order (
  AccountId BYTES(MAX),
  CustomerId BYTES(MAX),
  OrderId BYTES(MAX),
  Baz STRING(1024)
) PRIMARY KEY (AccountId, CustomerId, OrderId),
INTERLEAVE IN PARENT Customer;

The reason that you cannot interleave one table in two other tables in the way that you ask, is that interleaving tables actually means that Cloud Spanner will store the rows of the interleaved child table physically together with the parent table. There's no way to determine where to store the child rows if you were to interleave a table with two different, unrelated parent tables.

How can one interleave with two tables?

1 Answers1