turn an existing table into a SQL Graph Node or Edge

Question

working with an existing database, with implemented foreign keys, I want to use the SQL Graph new features of SQL Server 2017. To get started, I want to turn some existing tables into Node or Edge tables. What is the simplest way to do this kind of migration ?

Answer 1

https://argonsys.com/microsoft-cloud/library/graph-data-processing-with-sql-server-2017/

Can I alter an existing table into a node or edge table?

No. In the first release, ALTER TABLE to convert an existing relational table into a node or edge table is not supported. Users can create a node table and use INSERT INTO … SELECT FROM to populate data into the node table. To populate an edge table from an existing table, proper $from_id and $to_id values must be obtained from the node tables.

1) Node tables:

You have to disable foreign keys. How can foreign key constraints be temporarily disabled using T-SQL? And then make a copy of the table, drop it, recreate it as a Node, insert back the data, and finally switch foreign keys back on.

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

SELECT * 
INTO COPY_OF_REF_LOTS
FROM REF_LOTS;

DROP TABLE REF_LOTS;

CREATE TABLE REF_LOTS (ID INT PRIMARY KEY IDENTITY(1,1), myvalue varchar(100)) AS NODE;

INSERT INTO REF_LOTS
SELECT * FROM COPY_OF_REF_LOTS;

DROP TABLE COPY_OF_REF_LOTS;

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

2) Edge tables:

Create new edge tables and fill them up by retrieving $node_id from Node tables as explained in https://www.sqlshack.com/implement-graph-database-sql-server-2017/

Answer 2

Just create node and edge tables as additional independant table. Give them a FK to your existing tables. This way, the graph references your tables but does not modify your existing tables.