Avoiding circular reference - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Avoiding circular reference

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-11 : 18:33:30

Hi,

I have a table of categories. Each category has a parent category (except top level categories).

I want to avoid cirular references.

i.e. this shouldn't happen ( > = parent of )

cat1 > cat2 > cat1 > cat2....

Also, this shouldn't happen...

cat1 > cat2 > cat3 > cat4 > cat2 > cat3....

So in other words, when choosing a parent for category x, one must not be able to choose a category which x descends from.

My aim is to populate the drop down list that one chooses a parent from with only those valid categories. However, given that an update may take place before a selection has been submitted I think there should be a check upon insert or update.

I figure I can do this using a trigger and basically run through all the parent categories of the category being updated and check to see that they're all different to the category being presented as the new parent.

Should I also lock the whole table so that a concurrent update doesn't rearrange the hierarchy during the scan??

Cheers,

X-Factor

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-12 : 00:51:28

Do categories have a single parent? If so then just add a level to the table and make sure that no category has a parent of a lower level.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-12 : 06:18:50

Souonds like a good idea. Thanks.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-12 : 11:20:35

Is there a decent way to retrieve all the parent categories of a given category?

Thus if this is the situation...

cat1 > cat2 > cat3 > cat4

and I specify cat4 I get back cat1, cat2 and cat3.

At the moment, I've got a loop which keeps retrieving parent categories one by one until the top level is reached.

I've read the article which stores the linearage in a seperate field a bit like this...

1/2/3

But even then one would have to split up the string and do something like this which I'm not sure is any faster.

SELECT catName FROM categories WHERE id IN ( 1 , 2, 3 )

Cheers,

X-Factor

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-10-13 : 02:16:58

What if you combine the idea in Rob's article on trees, which you say you've already read, with his article on parsing CSVs into multiple rows? That would then provide you with a table you join on to get back to your catName.

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2003-10-13 : 19:44:28

quote:
just add a level to the table and make sure that no category has a parent of a lower level.

This doesn't actually work. If you have...

cat1 > cat2 > cat 3 > cat4

and

cat5 > cat6

Cat6 has a higher level than cat4 but there's no reson why cat4 shouldn't be a parent of cat6.

I figured that you could avoid this if you also had a field for 'thread' but then the same issue arises with branches of a thread and then things would get too complicated because branches could share categories.

So, I did this....


CREATE TRIGGER circularRef ON categories 
FOR UPDATE
AS

DECLARE @catID int
DECLARE @parentID int

SELECT @catID = categoryID, @parentID = parentID FROM inserted

IF @catID = @parentID
BEGIN
	RAISERROR 130000 'Circular Reference amongst categories' 
	ROLLBACK TRANSACTION
END

DECLARE @counter int
SET @counter = 0

--loop through parents looking for circular reference
WHILE @parentID IS NOT null and @counter < 5000
BEGIN

SELECT @parentID = parentID FROM categories WHERE categoryID = @parentID

IF @parentID = @catID 
BEGIN
	RAISERROR 130000 'Circular Reference amongst categories' 
	ROLLBACK TRANSACTION
END

SET @counter = @counter + 1 --safety valve
END

It appears to work and I don't think it needs any extra locking.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources