6.8 Loading SQL Property Graphs with Unsupported Key Types

If existing keys in a SQL graph cannot be loaded into the graph server (PGX), then generated keys maintained by the database may be used instead.

Consider the following SQL property graph which is defined with composite edge keys (USER1, USER2) for its edge table FRIENDS_WITH:

CREATE PROPERTY GRAPH SOCIAL_NETWORK
    VERTEX TABLES (
        ACCOUNT 
          KEY (ID) LABEL USER PROPERTIES (FULL_NAME, USERNAME)
    ) 
    EDGE TABLES (
        FRIENDS_WITH 
          KEY (USER1, USER2)
          SOURCE KEY (USER1) REFERENCES ACCOUNT (USERNAME)
          DESTINATION KEY (USER2) REFERENCES ACCOUNT (USERNAME)
          NO PROPERTIES
    )
    OPTIONS (TRUSTED MODE);

Although the SOCIAL_NETWORK graph can be loaded into the graph server (PGX), the edge keys will not be loaded. Also, subgraph loading is not supported for composite edge keys.

In order to resolve these issues, you can perform the following workaround steps on the underlying FRIENDS_WITH edge table.

1. Add a numeric key column to the FRIENDS_WITH table.

   ALTER TABLE FRIENDS_WITH ADD ID NUMBER(5) GENERATED ALWAYS AS IDENTITY;

   The data table of the FRIENDS_WITH provider now has an additional ID column which will automatically be populated with generated numeric keys.

   Note that using GENERATED AS IDENTITY columns require additional permissions in the database, such as CREATE ANY SEQUENCE.

2. Update the graph definition to use this new column as a key for the FRIENDS_WITH edge table.
   1. If you want to create a graph with the same name, then you must first drop the existing graph.

      DROP PROPERTY GRAPH SOCIAL_NETWORK;

   2. Update and run the new graph definition.

      CREATE PROPERTY GRAPH SOCIAL_NETWORK
          VERTEX TABLES (
              ACCOUNT 
                KEY (ID) 
                LABEL USER 
                PROPERTIES (FULL_NAME, USERNAME)
          ) 
          EDGE TABLES (
              FRIENDS_WITH 
                KEY (ID) 
                SOURCE KEY (USER1) REFERENCES ACCOUNT (USERNAME)
                DESTINATION KEY (USER2) REFERENCES ACCOUNT (USERNAME)
                NO PROPERTIES
          )
          OPTIONS (TRUSTED MODE);

      Alternatively, you may also use a CREATE OR REPLACE PROPERTY GRAPH statement, which will override a graph definition, if one with the same name exists already.

   The new graph definition supports subgraph loading using the SOCIAL_NETWORK SQL graph.