Manage a Spanner Graph schema

This document provides a comprehensive guide on managing Spanner Graph property schemas, detailing the processes for creating, updating, and dropping schemas using DDL statements.

For more information about property graph schemas, see the Spanner Graph schema overview. If you encounter errors when you create a property graph schema, see Troubleshoot Spanner Graph.

Create a property graph schema

To create a property graph schema, do the following:

Create the node input tables.
Create the edge input tables.
Define the property graph.

When you create a property graph schema, be sure to consider the best practices.

The following sections show how to create an example property graph schema:

Create node input tables

The following creates two node input tables, Person and Account, which serve as input for the node definitions in the example property graph:

CREATETABLEPerson(
idINT64NOTNULL,
nameSTRING(MAX),
birthdayTIMESTAMP,
countrySTRING(MAX),
citySTRING(MAX),
)PRIMARYKEY(id);
CREATETABLEAccount(
idINT64NOTNULL,
create_timeTIMESTAMP,
is_blockedBOOL,
nick_nameSTRING(MAX),
)PRIMARYKEY(id);

Create edge input tables

The following code creates two edge input tables, PersonOwnAccount and AccountTransferAccount, as input for the edge definitions in the example property graph:

CREATETABLEPersonOwnAccount(
idINT64NOTNULL,
account_idINT64NOTNULL,
create_timeTIMESTAMP,
FOREIGNKEY(account_id)REFERENCESAccount(id)
)PRIMARYKEY(id,account_id),
INTERLEAVEINPARENTPersonONDELETECASCADE;
CREATETABLEAccountTransferAccount(
idINT64NOTNULL,
to_idINT64NOTNULL,
amountFLOAT64,
create_timeTIMESTAMPNOTNULL,
order_numberSTRING(MAX),
FOREIGNKEY(to_id)REFERENCESAccount(id)
)PRIMARYKEY(id,to_id,create_time),
INTERLEAVEINPARENTAccountONDELETECASCADE;

Define a property graph

The following code defines the property graph using the CREATE PROPERTY GRAPH statement. This statement defines a property graph named FinGraph with Account and Person nodes, and PersonOwnAccount and AccountTransferAccount edges:

CREATEPROPERTYGRAPHFinGraph
NODETABLES(
Account,
Person
)
EDGETABLES(
PersonOwnAccount
SOURCEKEY(id)REFERENCESPerson(id)
DESTINATIONKEY(account_id)REFERENCESAccount(id)
LABELOwns,
AccountTransferAccount
SOURCEKEY(id)REFERENCESAccount(id)
DESTINATIONKEY(to_id)REFERENCESAccount(id)
LABELTransfers
);

Update a property graph schema

After you create a property graph schema, you update it by using the CREATE OR REPLACE PROPERTY GRAPH statement. This statement applies the changes by recreating the graph schema with the desired update.

You can make the following changes to a property graph schema:

Add a node or edge definition: Create the new input tables for the nodes and edges, and then use the CREATE OR REPLACE PROPERTY GRAPH statement to add the new definitions to the graph.
Update a node or edge definition: Update the underlying input table with new node and edge definitions. Then, use the CREATE OR REPLACE PROPERTY GRAPH statement to update the definitions in the graph.
Remove a node or edge definition: Use the CREATE OR REPLACE PROPERTY GRAPH statement and omit the definitions that you want to remove from the graph.

Add new node or edge definitions

To add a new node and a new edge definition, follow these steps:

Add a new node definition input table, Company, and a new edge definition input table, PersonInvestCompany.

CREATETABLECompany(
idINT64NOTNULL,
nameSTRING(MAX)
)PRIMARYKEY(id);
CREATETABLEPersonInvestCompany(
idINT64NOTNULL,
company_idINT64NOTNULL,
FOREIGNKEY(company_id)REFERENCESCompany(id)
)PRIMARYKEY(id,company_id),
INTERLEAVEINPARENTPersonONDELETECASCADE;

Update the FinGraph schema by adding the new Company node definition and the new PersonInvestCompany edge definition.

CREATEORREPLACEPROPERTYGRAPHFinGraph
NODETABLES(
Person,
Account,
Company
)
EDGETABLES(
AccountTransferAccount
SOURCEKEY(id)REFERENCESAccount
DESTINATIONKEY(to_id)REFERENCESAccount
LABELTransfers,
PersonOwnAccount
SOURCEKEY(id)REFERENCESPerson
DESTINATIONKEY(account_id)REFERENCESAccount
LABELOwns,
PersonInvestCompany
SOURCEKEY(id)REFERENCESPerson
DESTINATIONKEY(company_id)REFERENCESCompany
LABELInvests
);

Update node or edge definitions

To update an existing node or edge definition, you first alter the underlying input table, and then use the CREATE OR REPLACE PROPERTY GRAPH statement to apply the schema changes to the graph. To customize the properties exposed from the input tables, use the PROPERTIES clause. For more information, see Customize labels and properties.

The following steps show how to update the underlying table of a schema, then apply the update to the schema.

Add the mailing_address column to the Person underlying input table.
```
ALTERTABLEPerson
ADDCOLUMNmailing_addressSTRING(MAX);
```

Apply the changes to the Person table to the schema. Use the CREATE OR REPLACE PROPERTY GRAPH statement. The Person node definition reflects the updated Person table definition because the input table schema changed.

CREATEORREPLACEPROPERTYGRAPHFinGraph
NODETABLES(
Person,
Account
)
EDGETABLES(
AccountTransferAccount
SOURCEKEY(id)REFERENCESAccount
DESTINATIONKEY(to_id)REFERENCESAccount
LABELTransfers,
PersonOwnAccount
SOURCEKEY(id)REFERENCESPerson
DESTINATIONKEY(account_id)REFERENCESAccount
LABELOwns
);

Remove node or edge definitions

To remove existing node or edge definitions, recreate the property graph without those node or edge tables.

The following removes the Person node definition and the PersonOwnAccount edge definition by omitting them in the CREATE OR REPLACE PROPERTY GRAPH statement.

CREATEORREPLACEPROPERTYGRAPHFinGraph
NODETABLES(
Account
)
EDGETABLES(
AccountTransferAccount
SOURCEKEY(id)REFERENCESAccount
DESTINATIONKEY(to_id)REFERENCESAccount
LABELTransfers
);

Drop a property graph schema

To drop a graph schema from the underlying input tables, use the DROP PROPERTY GRAPH DDL statement. You can't delete the data from the underlying table when you drop a schema.

The following code drops the FinGraph property graph schema:

DROPPROPERTYGRAPHFinGraph;