CODEWITHSUNDEEP
google-cloud-platformgoogle-cloud-spanner
Jordi Torne
Jordi Torne

Reputation: 13

Using interleaved rows in Google Spanner without parent-child table relationship

I'm trying to define, using the interleaving approach of Google Spanner, a mechanism to have rows from several tables in the same split. According to the documentation (https://cloud.google.com/spanner/docs/schema-and-data-model#database-splits), rows that share the same primary key prefix are placed in the same split. But what is defining the "same primary key prefix"? Let's put an example. I've three tables with primary keys:

table A has PK (C1, CA)
table B has PK (C1, CB)
table C has PK (C1, CC)

These three tables share the first element of their primary key, column C1. I wish that all rows with the same value for C1 to go to the same split.

Can I define table A as the parent table for B and C? Do I need to create a dummy table with PK (C1)?

Any other approach?

The database will have lots of reads, many updates but few inserts.

Any suggestion will be highly appreciated.

Thanks

Upvotes: 1

Views: 1534

Answers (1)

John Corwin
John Corwin

Reputation: 359

In order to define a child table, the child table's primary key must contain the entire primary key of the parent as a prefix.

In your example, you would need to create a table with primary key C1. You could then interleave tables A, B, and/or C in this parent table.

The parent table can't quite be a dummy table. To insert a value into a child table, the corresponding row must exist in the parent table. So in your example, you would need to ensure that the parent table has a row for each value of C1 that you want to add to any of the child tables.

Upvotes: 3

Related Questions