Creating a Property Graph using PGQL

6.1 Creating a Property Graph using PGQL

CREATE PROPERTY GRAPH is a PGQL DDL statement to create a graph from database tables. The graph is stored in the property graph schema.

The CREATE PROPERTY GRAPH statement starts with the name you give the graph, followed by a set of vertex tables and edge tables. The graph can have no vertex tables or edge tables (an empty graph), or vertex tables and no edge tables (a graph with only vertices and no edges), or both vertex tables and edge tables (a graph with vertices and edges). However, a graph cannot be specified with only edge tables and no vertex tables.

Consider the following example:

PERSONS is a table with columns ID, NAME, and ACCOUNT_NUMBER. A row is added to this table for every person who has an account.
TRANSACTIONS is a table with columns FROM_ACCOUNT, TO_ACCOUNT, DATE, and AMOUNT. A row is added into this table in the database every time money is transferred from a FROM_ACCOUNT to a TO_ACCOUNT.

A straightforward mapping of tables to graphs is as follows. The graph concepts mapped are: vertices, edges, labels, properties.

Vertex tables: A table that contains data entities is a vertex table.
- Each row in the vertex table is a vertex.
- The columns in the vertex table are properties of the vertex.
- The name of the vertex table is the default label for this set of vertices. Alternatively, you can specify a label name as part of the CREATE PROPERTY GRAPH statement.
Edge tables: An edge table can be any table that links two vertex tables, or a table that has data that indicates an action from a source entity to a target entity. For example, a transfer of money from FROM_ACCOUNT to TO_ACCOUNT is a natural edge.
- Foreign key relationships can give guidance on what links are relevant in your data. CREATE PROPERTY GRAPH will default to using foreign key relationships to identify edges.
- Some of the properties of an edge table can be the properties of the edge. For example, an edge from FROM_ACCOUNT to TO_ACCOUNT can have properties DATE and AMOUNT.
- The name of an edge table is the default label for this set of edges. Alternatively, you can specify a label name as part of the CREATE PROPERTY GRAPH statement.
Keys:
- Keys in a vertex table: The key of a vertex table identifies a unique vertex in the graph. The key can be specified in the CREATE PROPERTY GRAPH statement; otherwise, it defaults to the primary key of the table. If there are duplicate rows in the table, the CREATE PROPERTY GRAPH statement will return an error.
- Key in an edge table: The key of an edge table uniquely identifies an edge in the graph. The KEY clause when specifying source and destination vertices uniquely identifies the source and destination vertices.
The following is an example CREATE PROPERTY GRAPH statement for the tables PERSONS and TRANSACTIONS.
```
CREATE PROPERTY GRAPH bank_transfers
     VERTEX TABLES (persons KEY(account_number))
     EDGE TABLES(
                  transactions KEY (from_acct, to_acct, date, amount)
                  SOURCE KEY (from_account) REFERENCES persons
                  DESTINATION KEY (to_account) REFERENCES persons
                  PROPERTIES (date, amount)
       )
```
Table aliases: Vertex and edge tables must have unique names. If you need to identify multiple vertex tables from the same relational table, or multiple edge tables from the same relational table, you must use aliases. For example, you can create two vertex tables PERSONS and PERSONS_ID from one table PERSONS, as in the following example.
```
CREATE PROPERTY GRAPH bank_transfers
     VERTEX TABLES (persons KEY(account_number)
                    persons_id AS persons KEY(id))
```
REFERENCES clause: This connects the source and destination vertices of an edge to the corresponding vertex tables.

For more details, see: https://pgql-lang.org/spec/latest/#creating-a-property-graph.

The following table lists the different ways you can create a property graph using the CREATE PROPERTY GRAPH statement:

Table 6-1 CREATE PROPERTY GRAPH Statement Support

Method	More Information
Create a property graph in the in-memory graph server (PGX) using the `oracle.pgx.api` Java package	Java APIs for Executing CREATE PROPERTY GRAPH Statements
Create a property graph in the in-memory graph server (PGX) using the `pypgx.api`Python package	Python APIs for Executing CREATE PROPERTY GRAPH Statements
Create a property graph in Oracle Database (Property Graph Schema) using the `oracle.pg.rdbms.pgql` Java package	Creating Property Graphs through CREATE PROPERTY GRAPH Statements
Create a property graph in Oracle Database (Property Graph Schema) using the `opgpy.pgql` Python package	Creating a Property Graph Using the Python Client
Create a property graph view on Oracle Database tables	Creating a Property Graph View

Parent topic: Property Graph Query Language (PGQL)