Materialized Path: Making Sure a Tree has No Cycles

The following DDL guarantees no cycles for Materialized Path:

CREATE TABLE dbo.OrgChart(GroupName VARCHAR(20) NOT NULL,
ParentPath VARCHAR(900) NULL,
FullPath VARCHAR(900) NOT NULL,
CONSTRAINT PK_OrgChart PRIMARY KEY(GroupName),
CONSTRAINT OrgChart_NoBackslashInGroupName CHECK(GroupName =
REPLACE(GroupName, '\', '')),
CONSTRAINT OrgChart_CorrectFullPath CHECK(FullPath = CASE WHEN
ParentPath IS NULL THEN '' ELSE ParentPath + '\' END
+ GroupName),
CONSTRAINT OrgChart_ParentPath_FK FOREIGN KEY(ParentPath) REFERENCES
OrgChart(FullPath),
CONSTRAINT OrgChart_NoCycles CHECK (ParentPath NOT LIKE GroupName +
'\%' AND ParentPath NOT LIKE '%\'+ GroupName + '\%')
)
go
SET NOCOUNT ON
-- adding some valid data
INSERT dbo.OrgChart(GroupName, ParentPath, FullPath) VALUES('US', NULL,
'US')
INSERT dbo.OrgChart(GroupName, ParentPath, FullPath)
VALUES('Chicagoland', 'US', 'US\Chicagoland')
INSERT dbo.OrgChart(GroupName, ParentPath, FullPath) VALUES('Loop
Office', 'US\Chicagoland', 'US\Chicagoland\Loop Office')
INSERT dbo.OrgChart(GroupName, ParentPath, FullPath)
VALUES('Naperville Lab', 'US\Chicagoland', 'US\Chicagoland\Naperville
Lab')
INSERT dbo.OrgChart(GroupName, ParentPath, FullPath)
VALUES('Lisle Datacenter', 'US\Chicagoland', 'US\Chicagoland\Lisle
Datacenter')