<\/i>One of four parts<\/strong><\/p>Welcome to part 2 of a four-part series on Supply Chains and Graph Databases. Each part explores different concepts that build upon the examples discussed in the other parts. You can use the following links to navigate directly to each part of the article.<\/div><\/p>\n In Azure SQL, nodes and edges are tables, emphasizing the simplicity of the process. When creating these tables, you append “AS NODE” and “AS EDGE” respectively to their create statements. The engine takes care of the rest, generating the necessary columns behind the scenes to enable graph functionality. In practical terms, nothing else changes. These tables function like any other tables, but they are ready for graph operations.<\/p>\n <\/p>\n Read more at<\/em>: SQL Graph CREATE TABLE Online Documentation<\/a><\/p>\n<\/blockquote>\n Interestingly, edge tables can have zero custom columns, representing an edge in your graph without any custom properties. It is unique in SQL to have a table with zero columns, made possible by the “ON EDGE” notation. <\/p>\n How does it work? These tables have graph columns under the hood. A node has $node_id, and an edge has, at a minimum, two: $from_id and $to_id. Each column is autogenerated and graph specific. We’ll look into more details about these special columns later.<\/p>\n <\/p>\n Read more at<\/em>: SQL Graph EDGE CONSTRAINT Online Documentation<\/a><\/p>\n<\/blockquote>\n An edge represents the relationship between two nodes. Like any data in SQL, referential integrity ensures that data engineers use tables correctly and that the data can be trusted. With edge constraints, SQL restricts which nodes an edge can reference. For example, “Plant-(produces)->Product” works, but “Plant-(produces)->Store” does not. These constraints are optional, similar to foreign key constraints in SQL Server. However, having a mechanism to help ensure data quality can be beneficial.<\/p>\n Inserting data into a graph table follows the same principles as inserting data into any other table. That said, inserting into an edge table is a bit unique. SQL Graph has three special columns across nodes and edges, each prefixed with a dollar sign. For nodes, the node key is represented by $node_id. As for edges, they have $from_id and $to_id. The edge columns define the “To” and “From” nodes, respectively. It may require some finesse in data engineering to write clean edge queries, but it is achievable. <\/p>\n <\/p>\n\n
Part 2: Enabling SQL Graph<\/h2>\n
Creating tables<\/h2>\n
CREATE TABLE Plant (Id INT, Name VARCHAR(50)) AS NODE;\nCREATE TABLE Product (Id INT, Name VARCHAR(50)) AS NODE;\nCREATE TABLE Warehouse (Id INT, Name VARCHAR(50)) AS NODE;\nCREATE TABLE Store (Id INT, Name VARCHAR(50)) AS NODE;\n\nCREATE TABLE produces AS EDGE;\nCREATE TABLE delivers_to (Miles INT NOT NULL) AS EDGE;\nCREATE TABLE ships_to (Miles INT NOT NULL) AS EDGE;\nCREATE TABLE stocks (Miles INT NOT NULL) AS EDGE;<\/code><\/pre>\n\n
![\"\"](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/06\/p2-02-NODEID.png\")
<\/p>\nALTER TABLE produces ADD CONSTRAINT EC_PRODUCES CONNECTION (Plant TO Product)\nALTER TABLE delivers_to ADD CONSTRAINT EC_DELIVERSTO CONNECTION (Plant TO Warehouse)\nALTER TABLE ships_to ADD CONSTRAINT EC_SHIPSTO CONNECTION (Warehouse TO Warehouse)\nALTER TABLE stocks ADD CONSTRAINT EC_STOCKS CONNECTION (Warehouse TO Store)<\/code><\/pre>\n\n
Inserting data<\/h2>\n
INSERT INTO Plant VALUES (1, 'TX'), (2, 'CO'), (3, 'KS'), (4, 'NM')\nINSERT INTO Product VALUES (1, 'Fish Tank'), (2, 'Dog House'), (3, 'Bird House')\nINSERT INTO Warehouse VALUES (1, 'NM'), (2, 'MN'), (3, 'WY'), (4, 'MO')\nINSERT INTO Store VALUES (1, 'KS'), (2, 'AR'), (3, 'AZ'), (4, 'NE')\n\nINSERT INTO produces ($from_id, $to_id)\nSELECT (SELECT $node_id FROM Plant WHERE Id = 1),\n (SELECT $node_id FROM Product WHERE Id = 1)<\/code><\/pre>\n\n