google-cloud-platformgoogle-cloud-spanner

Cloud Spanner doesn't enforce referential integrity between parent and child, What does this mean?


INTERLEAVE IN child tables share the same physical row interleaving characteristics, but Spanner doesn't enforce referential integrity between parent and child.

What is meant by this? Because I'm seeing these as well

"The parent row must exist before you can insert child rows. The parent row can either already exist in the database or can be inserted before the insertion of the child rows in the same transaction."

So what exactly does the Spanner won't enforce?


Solution

  • Spanner supports two options for physically storing parent and child records together:

    Examples:

    Referential Integrity is Enforced

    The INTERLEAVED IN PARENT keyword enforces the referential integrity between Singers and Albums.

    CREATE TABLE Singers (
     SingerId   INT64 NOT NULL PRIMARY KEY,
     FirstName  STRING(1024),
     LastName   STRING(1024),
     SingerInfo BYTES(MAX),
     );
    
    CREATE TABLE Albums (
     SingerId     INT64 NOT NULL,
     AlbumId      INT64 NOT NULL,
     AlbumTitle   STRING(MAX),
     ) PRIMARY KEY (SingerId, AlbumId),
    INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
    

    Referential Integrity is NOT Enforced

    The INTERLEAVED IN keyword specifies that the referential integrity between Singers and Albums does not need to be enforced. You can freely insert Album records before inserting the corresponding Singer records.

    CREATE TABLE Singers (
     SingerId   INT64 NOT NULL PRIMARY KEY,
     FirstName  STRING(1024),
     LastName   STRING(1024),
     SingerInfo BYTES(MAX),
     );
    
    CREATE TABLE Albums (
     SingerId     INT64 NOT NULL,
     AlbumId      INT64 NOT NULL,
     AlbumTitle   STRING(MAX),
     ) PRIMARY KEY (SingerId, AlbumId),
    INTERLEAVE IN Singers;
    

    See https://cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language#create_table for the full specification on these options.