Backing indexes of Cloud Spanner Foreign Keys

Test about The backing indexes of Cloud Spanner Foreign Keys.

Tested at GCPUG Shared Spanner on 2020-03-10.

DDL

CREATE TABLE Referenced (
  pk1 INT64 NOT NULL,
  pk2 INT64 NOT NULL,
  notnull1 INT64 NOT NULL,
  notnull2 INT64 NOT NULL,
  nullable1 INT64,
  nullable2 INT64,
) PRIMARY KEY(pk1, pk2);

CREATE TABLE Referencing (
  pk1 INT64 NOT NULL,
  pk2 INT64 NOT NULL,
  notnull1 INT64 NOT NULL,
  notnull2 INT64 NOT NULL,
  nullable1 INT64,
  nullable2 INT64,
  FOREIGN KEY(pk1) REFERENCES Referenced(pk1),
  FOREIGN KEY(pk1, pk2) REFERENCES Referenced(pk1, pk2),
  FOREIGN KEY(notnull1) REFERENCES Referenced(notnull1),
  FOREIGN KEY(notnull1, notnull2) REFERENCES Referenced(notnull1, notnull2),
  FOREIGN KEY(nullable1) REFERENCES Referenced(nullable1),
  FOREIGN KEY(nullable1, nullable2) REFERENCES Referenced(nullable1, nullable2),
) PRIMARY KEY(pk1, pk2);

CREATE TABLE Referencing2 (
  pk1 INT64 NOT NULL,
  pk2 INT64 NOT NULL,
  notnull1 INT64 NOT NULL,
  notnull2 INT64 NOT NULL,
  nullable1 INT64,
  nullable2 INT64,
  FOREIGN KEY(pk1) REFERENCES Referenced(pk1),
  FOREIGN KEY(pk1, pk2) REFERENCES Referenced(pk1, pk2),
  FOREIGN KEY(notnull1) REFERENCES Referenced(notnull1),
  FOREIGN KEY(notnull1, notnull2) REFERENCES Referenced(notnull1, notnull2),
  FOREIGN KEY(nullable1) REFERENCES Referenced(nullable1),
  FOREIGN KEY(nullable1, nullable2) REFERENCES Referenced(nullable1, nullable2),
) PRIMARY KEY(pk1, pk2)

Referencing and Referencing2 have the same definition.

Backing Indexes in INFORMATION_SCHEMA.INDEXES

$ gcloud spanner databases execute-sql --project=gcpug-public-spanner --instance=merpay-sponsored-instance apstndb-fk2 \
  --sql='SELECT TABLE_NAME, INDEX_NAME, INDEX_TYPE, IS_UNIQUE, IS_NULL_FILTERED, SPANNER_IS_MANAGED FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_SCHEMA = ""'
TABLE_NAME    INDEX_NAME                                               INDEX_TYPE   IS_UNIQUE  IS_NULL_FILTERED  SPANNER_IS_MANAGED
Referenced    IDX_Referenced_notnull1_notnull2_U_D6BEC427F9D9907A      INDEX        True       False             True
Referenced    IDX_Referenced_notnull1_U_7CF0CE3B85D70587               INDEX        True       False             True
Referenced    IDX_Referenced_nullable1_nullable2_U_EF6707DAE3EBFA92    INDEX        True       True              True
Referenced    IDX_Referenced_nullable1_U_3F0D584913280FC9              INDEX        True       True              True
Referenced    IDX_Referenced_pk1_U_BD1CA2AF713C6F8E                    INDEX        True       False             True
Referenced    PRIMARY_KEY                                              PRIMARY_KEY  True       False             False
Referencing   IDX_Referencing_notnull1_D590CCB10EF403FF                INDEX        False      False             True
Referencing   IDX_Referencing_notnull1_notnull2_FFBA4B6FB2040062       INDEX        False      False             True
Referencing   IDX_Referencing_nullable1_N_80512CFBB9071C71             INDEX        False      True              True
Referencing   IDX_Referencing_nullable1_nullable2_N_AA265040F131DACD   INDEX        False      True              True
Referencing   PRIMARY_KEY                                              PRIMARY_KEY  True       False             False
Referencing2  IDX_Referencing2_notnull1_0FE23E363F8D4A79               INDEX        False      False             True
Referencing2  IDX_Referencing2_notnull1_notnull2_8B5218A20C601DE1      INDEX        False      False             True
Referencing2  IDX_Referencing2_nullable1_N_593F28BC2EAC9863            INDEX        False      True              True
Referencing2  IDX_Referencing2_nullable1_nullable2_N_1BE3C4439CF7F296  INDEX        False      True              True
Referencing2  PRIMARY_KEY                                              PRIMARY_KEY  True       False             False

The format of the backing index name is IDX_{TABLE_NAME}_{INDEX_COLUMNS.join("_")}{SUFFIX}_{GENERATED_HEX}.
If there are multiple FK constraints that require the same index, they will be merged.
- Primary key prefix columns are treated as non-unique indexes

	column type	index	`IS_UNIQUE`	`IS_NULL_FILTERED`
Referencing	`PRIMARY KEY`	`PRIMARY KEY`
Referencing	`NOT NULL`	no suffix	`False`	`False`
Referencing	neither	suffixed by `_N`	`False`	`True`
Referenced	`PRIMARY KEY`	`PRIMARY KEY`
Referenced	`NOT NULL`	suffixed by `_U`	`True`	`False`
Referenced	neither	suffixed by `_N`	`True`	`True`

apstndb/SpannerFKBackingIndex.md

DDL

Backing Indexes in INFORMATION_SCHEMA.INDEXES

References