CODEWITHSUNDEEP
google-cloud-spanner
AJ Venturella
AJ Venturella

Reputation: 4912

Possible to interleave a new table into a secondary index table?

I'm gonna guess no, but secondary indexes seem a lot like tables in that you can directly select from them FORCE_INDEX and even JOIN on them:

JOIN MyTable@{FORCE_INDEX=anIndexToUseFromMyTable} AS myTable

So maybe you can create a new table interleaved into an index?

Example

CREATE TABLE Foo (
    primaryId STRING(64) NOT NULL,
    secondaryId STRING(64) NOT NULL,
    modifiedAt TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
) PRIMARY KEY (primaryId);

-- Index we would like to interleave into for another table
CREATE INDEX FooSecondaryIdIndex ON Foo(secondaryId);

-- interleave this table into the index above
-- and support DELETE CASCADE
CREATE TABLE Bar (
    secondaryId STRING(64) NOT NULL,
    extraData STRING(64) NOT NULL,
    modifiedAt TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
) PRIMARY KEY (secondaryId),
INTERLEAVE IN PARENT Foo@{FORCE_INDEX=FooSecondaryIdIndex} ON DELETE CASCADE;

Upvotes: 0

Views: 148

Answers (1)

AJ Venturella
AJ Venturella

Reputation: 4912

Well... it doesn’t look like that is supported:

Error parsing Spanner DDL statement: CREATE TABLE Bar ( secondaryId STRING(64) NOT NULL, extraData STRING(64) NOT NULL, modifiedAt TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true), ) PRIMARY KEY (secondaryId), INTERLEAVE IN PARENT Foo@{FORCE_INDEX=FooSecondaryIdIndex} ON DELETE CASCADE : Syntax error on line 6, column 25: Expecting 'EOF' but found '@'

Upvotes: 1

Related Questions