The first section of this chapter discussed how to use the INSERT statement to load data into node and edge tables. This section explains the other two modification operations, DELETE and UPDATE. Generally, deletion of rows in node and edge tables can be performed without any restrictions, while modification of values of columns is limited and depends on the type of the particular column.
1. Deleting Graph Data
Before we discuss how SQL Server Graph Databases deletes instances of node and edge tables, Example 31.18 provides one more look at how data is inserted into an edge table.
Example 31.18
USE graph_db;
INSERT into LivesIn VALUES ((SELECT $node_id FROM Employee WHERE id = 6),
(SELECT $node_id FROM City WHERE id = 5), ‘2.1.2018’);
The INSERT statement in Example 31.18 adds a new instance of the relationship LivesIn. This instance connects instance 6 of the Employee node table with instance 5 of the City node table. With the query in Example 31.19, you can check whether this instance is properly inserted.
Example 31.19
USE graph_db;
SELECT E.Name, C.Name
FROM Employee E, LivesIn, City C
WHERE MATCH(E -(LivesIn)->C) AND E.id = 6 AND C.id = 5;
The result is
The query in Example 31.19 is based on two $node_id values. One approach to delete this instance is to use two nested SELECT statements, as shown in Example 31.20 (which is the same way they are used in Example 31.18). Note that the deletion is based on the values of the $from_id and $to_id columns of the LivesIn table and the corresponding values of $node_id.
Example 31.20
USE graph_db;
DELETE FROM LivesIn
WHERE $from_id =(SELECT $node_id FROM Employee WHERE id = 6)
AND $to_id = (SELECT $node_id FROM City WHERE id = 5);
Another way to delete this instance of the relationship LivesIn is to use the MATCH function, as shown in Example 31.21. (Note that the syntax of the DELETE statement does not correspond to the standardized syntax of that statement. It is a proprietary extension in SQL Server.)
Example 31.21
USE graph_db;
DELETE LivesIn
FROM Employee E, LivesIn, City C
WHERE MATCH(E-(LivesIn)->C) AND E.id = 6 AND C.id = 5;
The MATCH function in Example 31.21 searches for a pattern based on the specified relationship. In this case, MATCH selects all rows of the LivesIn relationship. The other two conditions in the WHERE clause of the query restrict the deletion to the particular instance of that relationship.
2. Updating Graph Data
In contrast to the DELETE statement, the UPDATE statement has some limitations. Generally, you can apply the UPDATE statement only to modify the values of the user-defined columns, as demonstrated in Example 31.22.
Example 31.22
USE graph_db;
UPDATE WorksIn SET Starts = 2020
WHERE $from_id = (SELECT $node_id FROM Employee WHERE id = 6)
AND $to_id = (SELECT $node_id FROM Company WHERE id = 1);
Example 31.22 modifies the value of the Starts column of the WorksIn edge table by using the values of the $node_id columns of the originating and terminating tables.
3. Editing Information Concerning SQL Server Graph Databases
To edit information concerning SQL Server Graph Databases, you can use either catalog views or system functions, both of which are described in the following subsections.
3.1. Graph Databases: Catalog Views
Two catalog views, sys.tables and sys.columns, have been extended to contain metadata concerning graph databases. Also, two new catalog views, sys.edge_constraints and sys.edge_ constraint_clauses, are used to edit metadata concerning existing edge constraints.
As you already know, when you create a node table or an edge table, the system adds two new columns of the BIT data type, is_node and is_edge, to the sys.tables catalog view. For a node table, the value of is_node is set to 1, and the value of is_edge is set to 0. Conversely, the values of the is_node and is_edge columns of an edge table are 0 and 1, respectively.
Example 31.23 retrieves the names of all tables of the graph_db database that are either node tables or edge tables. (The corresponding values of the is_node and is_edge columns are displayed, too.)
Example 31.23
USE graph_db;
SELECT name, is_node, is_edge
FROM sys.tables
WHERE is_node = 1 OR is_edge = 1;
The result is
The sys.columns catalog view has been extended with two new columns: graph_type and graph_type_desc. They indicate the types of columns that the Database Engine generated. The type is indicated by a predefined numerical value and its related description. Microsoft does not provide a great deal of specifics about the columns, but you can find some details in the Microsoft documentation.
Example 31.24 uses the sys.edge_constraints and sys.edge_constraint_clauses catalog views to display metadata concerning existing edge constraints in the LivesIn edge table.
Example 31.24
USE graph_db;
SELECT
EC.name AS Edge_constraint
, OBJECT_NAME(EC.parent_object_id) AS Edge_table
, OBJECT_NAME(ECC.from_object_id) AS From_node_table
, OBJECT_NAME(ECC.to_object_id) AS To_node_table
FROM sys.edge_constraints EC
INNER JOIN sys.edge_constraint_clauses ECC
ON EC.object_id = ECC.object_id
WHERE EC.parent_object_id = object_id(‘LivesIn’);
The result is
In Example 31.24, the sys.edge_constraints and sys.edge_constraint_clauses catalog views are joined together using the object_id column to display the information concerning the specified edge constraint (see Example 31.3). The constraint name (Emp_to_City) and the name of the corresponding edge table (LivesIn) are found in the sys.edge_constraints catalog view. Similarly, the names of the corresponding node tables are found in the sys.edge_ constraint_clauses catalog view.
3.2. Graph Databases: System Functions
As you already know from Chapter 9, system functions are used to access catalog views. SQL Server contains six system functions related to Graph Databases. These functions are described in Table 31-1.
The next two examples demonstrate how these system functions can be used. Example 31.25 uses the NODE_ID_FROM_PARTS function.
Example 31.25
USE graph_db;
SELECT NODE_ID_FROM_PARTS(OBJECT_ID(‘dbo.Company’), 0);
The result is
{“type”:”node”,”schema”:”dbo”,”table”:”Company”,”id”:0}
Example 31.25 constructs a JSON document for a given node_id value from an object_id value, with the help of the NODE_ID_FROM_PARTS system function. This function has two parameters: the first is object_id value of the corresponding node table, and the second specifies the value of the graph_id column. As you can see from the result of this example, it returns the JSON document with four name/value pairs that correspond to the displayed value of the first row (ID = 0) in the Company node table.
NOTE The practical use of the NODE_ID_FROM_PARTS system function, as shown in Example 31.25, is in the case that you want to load data from another source and intend to assign the existing ID to each row as the graph ID.
Example 31.26 shows how the NODE_ID_FROM_PARTS function can be used to insert a row in an edge table.
Example 31.26
DECLARE @table1 INT = OBJECT_ID(‘dbo.Company’);
DECLARE @table2 INT = OBJECT_ID(‘dbo.City’);
INSERT INTO LocatedIn ($from_id, $to_id)
VALUES (NODE_ID_FROM_PARTS(@table1, 1),
NODE_ID_FROM_PARTS(@table2, 2));
The batch in Example 31.26 obtains the object IDs from the relationship’s originating and terminating node tables (Company and City) and saves them into the @table1 and @table2 variables, respectively. These variables are then used as the parameters of the NODE_ID_ FROM_PARTS function to insert a new row into the LocatedIn edge table.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.