Demo creation of tables with system-named constraints

system_named_constraints_demo.sql

DROP TABLE IF EXISTS [dbo].[TestTable2];
DROP TABLE IF EXISTS [dbo].[TestTable1];
DROP TABLE IF EXISTS dbo.TestEdgeTable;
DROP TABLE IF EXISTS dbo.TestNodeTable1;
DROP TABLE IF EXISTS dbo.TestNodeTable2;
DROP TABLE IF EXISTS dbo.TestNodeTable3;
GO
IF SCHEMA_ID('EitanTest') IS NOT NULL DROP SCHEMA EitanTest;
GO
CREATE SCHEMA EitanTest AUTHORIZATION dbo;
GO

CREATE TABLE EitanTest.[TestTable1](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[InsertTime] [datetime] NOT NULL DEFAULT (GETUTCDATE()),
	[Salary] [int] NOT NULL CHECK ([Salary]>=0),
	[Email] nvarchar(128) NOT NULL UNIQUE CHECK ([Email] LIKE N'_%@_%._%'),
	[ParentID] [int] NULL FOREIGN KEY REFERENCES EitanTest.[TestTable1](ID),
	CHECK ([InsertTime] > '2000-01-01' AND [Salary] > 1)
);

CREATE TABLE [dbo].[TestTable2](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[HeadID] [int] NOT NULL FOREIGN KEY REFERENCES EitanTest.[TestTable1] ([ID]),
	[Email2] nvarchar(128) NULL FOREIGN KEY REFERENCES EitanTest.[TestTable1] ([Email])
);

-- If SQL 2019 or newer
IF (CONVERT(FLOAT, (@@microsoftversion / 0x1000000) & 0xff)) >= 15
BEGIN
	EXEC(N'
		-- CREATE node and edge tables
		CREATE TABLE dbo.TestNodeTable1
		(
		ID INTEGER PRIMARY KEY
		, CustomerName VARCHAR(100)
		)
		AS NODE;

		CREATE TABLE dbo.TestNodeTable2
		(
		ID INTEGER PRIMARY KEY
		, SupplierName VARCHAR(100)
		)
		AS NODE;

		CREATE TABLE dbo.TestNodeTable3
		(
		ID INTEGER PRIMARY KEY
		, ProductName VARCHAR(100)
		)
		AS NODE;

		-- CREATE edge table with edge constraints.
		CREATE TABLE dbo.TestEdgeTable
		(
		PurchaseCount INT
		, CONNECTION (dbo.TestNodeTable1 TO dbo.TestNodeTable3, dbo.TestNodeTable2 TO dbo.TestNodeTable3)
		, CONNECTION (dbo.TestNodeTable2 TO dbo.TestNodeTable1)
		)
		AS EDGE;'
	);
END