CODEWITHSUNDEEP
parent-childprimary-keygoogle-cloud-spanner
Peter Littig
Peter Littig

Reputation: 187

Can a Cloud Spanner child table have the same primary key as the parent table?

Can a Cloud Spanner child table define the same primary key that the parent table uses (with the child is interleaved in the parent)? I know the child's primary key must use the parent's primary key as a prefix, but is the child key required to use at least one additional column? If it's permitted to use the same key, is it bad practice? And if it's not permitted, why not?

For example:

CREATE TABLE Furniture (
  FurnitureId STRING(MAX) NOT NULL,
  MakerId INT64 NOT NULL,
  // additional fields here...
) PRIMARY KEY (FurnitureId, MakerId);

CREATE TABLE FurnitureHistory (
  FurnitureId STRING(MAX) NOT NULL,
  MakerId INT64 NOT NULL,
  // additional fields here...
) PRIMARY KEY (FurnitureId, MakerId),
  INTERLEAVE IN PARENT Furniture;

Thanks! Peter

Upvotes: 0

Views: 363

Answers (1)

Knut Olav Løite
Knut Olav Løite

Reputation: 3512

It is permitted, but it seems a little bit strange at first hand. This data model will allow you to insert at most one child row for each parent row. This is quite similar to adding a couple of optional fields to the parent table, but with these optional fields logically separated from the parent table. So I guess this makes sense if:

  • You have a number of additional fields that have a logical different meaning, which gives you a reason for storing them in a child table.

And/or

  • The additional fields should either not be filled at all, or all (or at least more than one) should be filled when one of them are filled.

Upvotes: 1

Related Questions