I am using Cloud Spanner and am aware that one can interleave multiple tables eg: Singers > Albums > Songs (Albums is interleaved in Singers and Songs is interleaved in Albums).
These tables have the primary keys singerId, albumId, songId respectively. I will keep this example to phrase my question better.
I create a new song called song1 which is from album1 by singer1.
Then when I try to create another song called song1 from album1 with singerId singer2, I get the error:
Query failed: Unique index violation on index IDX_Songs_songId_U_1A1B6C1202613A13 at
index key [song1,singer1,album1]. It conflicts with row [song1,singer1,album1] in table
Songs.
Does the new Song entry need to have unique keys within the Songs and Albums tables even though the outermost singerId key is different?
In short, I want to know why [singer1,album1,song1] and [singer2,album1,song1] are seen as duplicates even though their parent key differs.
The minimal DDL is given below:
Singers
CREATE TABLE Singers (
singerId STRING(MAX) NOT NULL,
firstName STRING(50) NOT NULL,
surname STRING(50) NOT NULL,
status INT64,
) PRIMARY KEY(singerId);
Albums
CREATE TABLE Albums (
singerId STRING(MAX) NOT NULL,
albumId STRING(MAX) NOT NULL,
releaseDate DATE,
genre BOOL,
) PRIMARY KEY(singerId, albumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
Songs
CREATE TABLE Songs (
singerId STRING(MAX) NOT NULL,
albumId STRING(MAX) NOT NULL,
songId STRING(MAX) NOT NULL,
duet BOOL,
) PRIMARY KEY(singerId, albumId, songId),
INTERLEAVE IN PARENT Albums ON DELETE CASCADE;