Defining interleaved table with different foreign key in Google Cloud Spanner

Question

I am trying to define interleave tables and it works when we have same column name of parent tables primary and interleaved tables foreign key. I am already migrating my database from mysql to spanner. All tables have 'id' as primary key column name.

Please consider below example:

CREATE TABLE Singers (
  Id   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
) PRIMARY KEY (Id);

CREATE TABLE Albums (
  SingerId     INT64 NOT NULL,
  Id      INT64 NOT NULL,
  AlbumTitle   STRING(MAX),
) PRIMARY KEY (SingerId, Id),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

This is not working for me, because parent (Singer) table has 'id' as a primary key column and child table has 'SingerId' as a foreign key.

marian.vladoi · Answer 1 · 2020-08-12T07:34:55.980

1

I was able to create the tables using Google Cloud Console.

Here you can find the Equivalent DDL:


CREATE TABLE Singers (
    Id INT64 NOT NULL,
    FirstName STRING(MAX),
    LastName STRING(MAX),
    SingerInfo STRING(MAX),
) PRIMARY KEY (Id)

CREATE TABLE Albums (
    Id INT64 NOT NULL,
    SingerId INT64 NOT NULL,
    AlbumeTitle STRING(MAX),
) PRIMARY KEY (Id, SingerId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE

It seems that the order of the columns matters.

The primary key from parent should be first, as it is inherited.

##EDIT

It was working on the Console UI

edited Aug 12 '20 at 07:34

answered Aug 11 '20 at 09:16

marian.vladoi

7,663
1
15
29

DB::table('Singers')->insert([ 'Id' => 1234, 'FirstName' => 'Sahilesh', 'LastName' => "Matariya", 'SingerInfo' => 'bla bla' ]); DB::table('Albums')->insert([ 'Id' => 9876, 'SingerId' => 1234, 'AlbumeTitle' => 'sdfsdf' ]); It is not allowing me to add above record. I got below error. "Insert failed because key was not found in parent table: Parent Table: Singers Child Table: Albums Key: {Int64(9876)}" – Shailesh Matariya Aug 11 '20 at 17:09
It must require same value for ID field in both tables.It is still considering Id field from Albums table as a foreign key. – Shailesh Matariya Aug 11 '20 at 17:12
Please refer these tables, but it is cockroach db: https://www.cockroachlabs.com/docs/stable/interleave-in-parent.html#interleaving-tables – Shailesh Matariya Aug 11 '20 at 17:22
Please check the SingerId value in the Albums table, it is different than id value in Singers table. I want SingerId value 1234 and Album id value 9876. Select Album.Id, Album.AlbumTitle, Singers.FirstName from Albums join Singers on Albums.SingerId = Singers.Id; This query should work. – Shailesh Matariya Aug 13 '20 at 09:40
From my understanding the Singers id value and the Album Id value should be the same, because the Album table inherited the Singers Id . – marian.vladoi Aug 19 '20 at 10:24
Let's take a simple example: We have users and posts tables, User can have many posts, so user table's primary key "id" should be foreign key in posts table as 'user_id', right? – Shailesh Matariya Aug 19 '20 at 10:40
Unfortunately this schema does not do what OP originally intended. Here in Albums table, SinglerId is the second component of primary key, while the album Id is the first. The interleave specification tells Spanner to map Albums(Id) to Singesr(SingerId), which was the reason why your examples to insert a new album with Id 9876 fails. – X.J Aug 30 '20 at 04:04

score 0 · Answer 2 · answered Aug 11 '20 at 16:41

0

Yeah, the order of primary key specification matters.

More specifically:

If you declare a table to be a child of another table, the primary key column(s) of the parent table must be the prefix of the primary key of the child table. This means if a parent table's primary key is composed of N columns, the primary key of each of its child tables must also be composed of those same N columns, in the same order and starting with the same column.

Reference : https://cloud.google.com/spanner/docs/schema-and-data-model#primary_keys

answered Aug 11 '20 at 16:41

Lakshmi Pallikila

336
1
3

I already read this documentation and I followed this pattern. SingerId is already prefixed in the Album table's primary key. Can you please tell me what's wrong in my schema? – Shailesh Matariya Aug 11 '20 at 17:17
Please refer these tables, but it is cockroach db: https://www.cockroachlabs.com/docs/stable/interleave-in-parent.html#interleaving-tables – Shailesh Matariya Aug 11 '20 at 17:22

score 0 · Answer 3 · answered Aug 30 '20 at 04:12

Just switch order of Albums primary key should fix the issue:

CREATE TABLE Albums (
    SingerId INT64 NOT NULL,
    Id INT64 NOT NULL,
    AlbumeTitle STRING(MAX),
) PRIMARY KEY (SingerId, Id),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE

By the way, if you just need general foreign key relationship, but not physical data interleaving, you might want to consider the more generic foreign key in Cloud Spanner. You can refer to docs here: https://cloud.google.com/spanner/docs/foreign-keys/overview

To use a generic foreign key rather than interleaving, the albums table would look like this (you'd be able to maintain the old order of the primary key)

CREATE TABLE Albums (
    Id INT64 NOT NULL,
    SingerId INT64 NOT NULL,
    AlbumeTitle STRING(MAX),
    FOREIGN KEY (SingerId) REFERENCES Singers(SingerId),
) PRIMARY KEY (Id, SingerId)

The downside is that you can't use cascade deletes as it's not supported in the generic foreign key yet.

We are already using generic foreign key relationship at the moment. Interleaved relation is not working for me. Can you please try with seeding the database for the first case of your answer. — Shailesh Matariya, Aug 31 '20 at 16:20

Defining interleaved table with different foreign key in Google Cloud Spanner

3 Answers3