Cloud Spanner Unique index violation

Viewed 47

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;
1 Answers

Does the new Song entry need to have unique keys within the Songs and Albums tables even though the outermost singerId key is different?

Short answer is no.

What happened in your case is that there exists a unique index (IDX_Songs_songId_U_1A1B6C1202613A13) that enforces the uniqueness of "songId" itself.

It would be good to paste the schema here so we can further discuss the use case.

Related