How to avoid circular relationship in SQL-Server?

Question

I am creating a self-related table:

Table Item columns:
ItemId int - PK;
Amount money - not null; Price money - a computed column using a UDF that retrieves value according to the items ancestors' Amount.
ParentItemId int - nullable, reference to another ItemId in this table.

I need to avoid a loop, meaning, a sibling cannot become an ancestor of his ancestors, meaning, if ItemId=2 ParentItemId = 1, then ItemId 1 ParentItemId = 2 shouldn't be allowed.

I don't know what should be the best practice in this situation. I think I should add a CK that gets a Scalar value from a UDF or whatever else.

EDIT: Another option is to create an INSTEAD OF trigger and put in 1 transaction the update of the ParentItemId field and selecting the Price field from the @@RowIdentity, if it fails cancel transaction, but I would prefer a UDF validating.

Any ideas are sincerely welcomed.

Answer 1

How big of a problem is this, in real life? It can be expensive to detect these situations (using a trigger, perhaps). In fact, it's likely going to cost you a lot of effort, on each transaction, when only a tiny subset of all your transactions would ever cause this problem.

Think about it first.

Answer 2

A simple trick is to force the ParentItemId to be less than the ItemId. This prevents loop closure in this simple context.

However, there's a down side - if you need for some reason to delete/insert a parent, you may need to delete/insert all of its children in order as well.

Equally, hierarchies need to be inserted in order, and you may not be able to reassign a parent.

Answer 3

Does this definitely need to be enforced at the database level?

I'm only asking as I have databases like this (where the table similar to this is like a folder) and I only make sure that the correct parent/child relationships are set up in the application.

Answer 4

Checks like this is not easy to implement, and possible solutions could cause a lot of bugs and problems may be harder then initial one. Usually it is enough to add control for user's input and prevent infinite loop on read data. If your application uses stored procedures, no ORM, than I would choose to implement this logic in SP. Otherwise - handle it in other layers, not in DB

Answer 5

Tested and works just great:

CREATE TRIGGER Item_UPDATE
   ON Item
   FOR INSERT, UPDATE
AS 
BEGIN

BEGIN TRY
    SELECT Price FROM INSERTED
END TRY
BEGIN CATCH
    RAISERROR('This item cannot be specified with this parent.', 16, 1)
    ROLLBACK TRANSACTION;
END CATCH

END
GO