Detecting Circular References.

Greetings All, this is my first time dealing with this particular issue
so I figured I would solicit your expertise :)
Annoted Version of DDL:
Create Table Company
(
ParentId int null,
PrimaryId int not null primary key,
CompanyName nvarchar(200) not null
)
Facts:
1.) PrimaryId is the PK of the Company.
2.) ParentId is the identifier of the Parent company if one exists.
3.) If ParentId is null then PrimaryId is its own parent.
4.) A company can have only one parent (hierarchcial graph).
5.) There is no one master company for all PrimaryId's. This is to say
you can have more than one hierarchy represented in the Company table:
A D
/ \ / \
B C E F
Where A and D are there own parents.
Problem:
I need to make sure that there can never be the case where a company is
simultaneously both the child and parent of another company, e.g.)
A is parent of B
B is parent of C
C is parent of D
D is parent of A <= Violation
Any help would be greatly appreciated.
TFDThis technique should work:
SET NOCOUNT ON
-- DROP TABLE Company
GO
Create Table Company
(
ParentId int null,
PrimaryId int not null primary key,
CompanyName nvarchar(200) --not null
)
GO
INSERT Company (ParentId, PrimaryId)
SELECT NULL, 1
UNION
SELECT 1, 2
UNION
SELECT 2, 3
UNION
SELECT 2, 4
UNION
SELECT 3, 5
UPDATE Company
SET ParentID = 5
WHERE PrimaryID = 1
GO
SELECT ParentId --Ancestor
, PrimaryId --Descendant
INTO #Hierarchy
FROM Company
WHERE ParentId IS NOT NULL
DECLARE @.BadHierarchy CHAR(1)
SET @.BadHierarchy = 'N'
WHILE 1 = 1 BEGIN
INSERT #Hierarchy
SELECT DISTINCT
p.ParentId
, c.PrimaryId
FROM #Hierarchy p
INNER JOIN #Hierarchy c
ON p.PrimaryId = c.ParentId
WHERE NOT EXISTS (SELECT * FROM #Hierarchy WHERE ParentID =
p.ParentID AND PrimaryId = c.PrimaryId)
IF EXISTS (SELECT * FROM #Hierarchy a WHERE EXISTS (SELECT * FROM
#Hierarchy b WHERE a.PrimaryID = b.ParentID AND a.ParentID =
b.PrimaryID)) BEGIN
SET @.BadHierarchy = 'Y'
BREAK
END
IF @.@.ROWCOUNT = 0 BREAK
END
SELECT @.BadHierarchy
SELECT * FROM #Hierarchy
DROP TABLE #Hierarchy|||I goofed on that one. That @.@.ROWCOUNT needs to be assigned to a
variable before the IF EXISTS statement above it, and that variable
needs to be checked where I'm checking @.@.ROWCOUNT.
-Alansql