Create, update, or drop a Spanner Graph schema

This document shows you how to create, update or drop a property graph in Spanner Graph using the example schema that you created in Set up and query Spanner Graph.

Spanner lets you make schema updates with no downtime. You can update the schema of an existing database in any of the following ways:

The Google Cloud console

Submit a command on the Spanner Studio page.

To access the Spanner Studio page, click Spanner Studio from the Database overview or Table overview page. For more information about accessing Spanner Studio, see Manage your data using the Google Cloud console.
The gcloud spanner command-line tool

Submit a command by using the gcloud spanner databases ddl update command.
The client libraries
The projects.instances.databases.updateDdl REST API
The UpdateDatabaseDdl RPC API

Create a property graph schema

For more information about property graph schemas, see the Spanner Graph schema overview.

To create a property graph schema, follow these steps:

Create node input tables Person and Account. These tables are used as input tables for the node definitions in the example property graph.

CREATE TABLE Person (
  id               INT64 NOT NULL,
  name             STRING(MAX),
  birthday         TIMESTAMP,
  country          STRING(MAX),
  city             STRING(MAX),
) PRIMARY KEY (id);

CREATE TABLE Account (
  id               INT64 NOT NULL,
  create_time      TIMESTAMP,
  is_blocked       BOOL,
  nick_name        STRING(MAX),
) PRIMARY KEY (id);

Create the edge input tables PersonOwnAccount and AccountTransferAccount. These tables are used as input tables for the edge definitions in the example property graph.

CREATE TABLE PersonOwnAccount (
  id               INT64 NOT NULL,
  account_id       INT64 NOT NULL,
  create_time      TIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES Account (id)
) PRIMARY KEY (id, account_id),
  INTERLEAVE IN PARENT Person ON DELETE CASCADE;

CREATE TABLE AccountTransferAccount (
  id               INT64 NOT NULL,
  to_id            INT64 NOT NULL,
  amount           FLOAT64,
  create_time      TIMESTAMP NOT NULL,
  order_number     STRING(MAX),
  FOREIGN KEY (to_id) REFERENCES Account (id)
) PRIMARY KEY (id, to_id, create_time),
  INTERLEAVE IN PARENT Account ON DELETE CASCADE;

Define the property graph using the example CREATE PROPERTY GRAPH statement.

The following example defines a property graph named FinGraph with Account and Person nodes, and PersonOwnAccount and AccountTransferAccount edges.

CREATE PROPERTY GRAPH FinGraph
  NODE TABLES (
    Account,
    Person
  )
  EDGE TABLES (
    PersonOwnAccount
      SOURCE KEY (id) REFERENCES Person (id)
      DESTINATION KEY (account_id) REFERENCES Account (id)
      LABEL Owns,
    AccountTransferAccount
      SOURCE KEY (id) REFERENCES Account (id)
      DESTINATION KEY (to_id) REFERENCES Account (id)
      LABEL Transfers
  );

This example follows these best practices:

For more information, see Best practices for graph schema design.

For information about common Spanner Graph schema errors, see Troubleshoot Spanner Graph.

Update a property graph schema

You can update a property graph schema in the following ways:

Add new node or edge definitions.
Update existing node or edge definitions.
Remove existing node or edge definitions.

In each case, you must recreate the property graph with the updated schema.

Add new node or edge definitions

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

Add new input tables.

Define the input tables associated with the new graph element definitions. See the following example, which adds two new input tables Company and PersonInvestCompany:

CREATE TABLE Company (
  id INT64 NOT NULL,
  name STRING(MAX)
) PRIMARY KEY (id);

CREATE TABLE PersonInvestCompany (
  id INT64 NOT NULL,
  company_id INT64 NOT NULL,
  FOREIGN KEY (company_id) REFERENCES Company (id)
) PRIMARY KEY (id, company_id),
  INTERLEAVE IN PARENT Person ON DELETE CASCADE;

Update the graph with CREATE OR REPLACE PROPERTY GRAPH. The following example updates the FinGraph schema by adding a new node definition Company and a new edge definition PersonInvestCompany:

CREATE OR REPLACE PROPERTY GRAPH FinGraph
  NODE TABLES (
    Person,
    Account,
    Company
  )
  EDGE TABLES (
    AccountTransferAccount
      SOURCE KEY (id) REFERENCES Account
      DESTINATION KEY (to_id) REFERENCES Account
      LABEL Transfers,
    PersonOwnAccount
      SOURCE KEY (id) REFERENCES Person
      DESTINATION KEY (account_id) REFERENCES Account
      LABEL Owns,
    PersonInvestCompany
      SOURCE KEY (id) REFERENCES Person
      DESTINATION KEY (company_id) REFERENCES Company
      LABEL Invests
  );

Update existing node or edge definitions

To update existing node or edge definitions, follow these steps:

Update the input table schema. The following example adds the mailing_address column to the input table Person.
```
ALTER TABLE Person
ADD COLUMN mailing_address STRING(MAX);
```
Update the property graph schema with CREATE OR REPLACE PROPERTY GRAPH. The following example adds a new property mailing_address to the Person node definition using the CREATE OR REPLACE PROPERTY GRAPH statement. In this example, the Person node definition automatically picks up the altered Person table definition because the input table schema changed.
```
CREATE OR REPLACE PROPERTY GRAPH FinGraph
  NODE TABLES (
    Person,
    Account
  )
  EDGE TABLES (
    AccountTransferAccount
      SOURCE KEY (id) REFERENCES Account
      DESTINATION KEY (to_id) REFERENCES Account
      LABEL Transfers,
    PersonOwnAccount
      SOURCE KEY (id) REFERENCES Person
      DESTINATION KEY (account_id) REFERENCES Account
      LABEL Owns
  );
```
You can also use the PROPERTIES clause() to customize the properties exposed from the input tables. For more information, see Customize labels and properties.

Remove existing node or edge definitions

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

The following example removes the Person node definition and the PersonOwnAccount edge definition.

CREATE OR REPLACE PROPERTY GRAPH FinGraph
  NODE TABLES (
    Account
  )
  EDGE TABLES (
    AccountTransferAccount
      SOURCE KEY (id) REFERENCES Account
      DESTINATION KEY (to_id) REFERENCES Account
      LABEL Transfers
  );

Drop a property graph schema

You can use the DROP PROPERTY GRAPH DDL statement to drop your graph schema.

The following example drops the FinGraph property graph schema:

DROP PROPERTY GRAPH FinGraph;