Restrict database tree depth

Typically when I represent a parent child hierarchy I have a table as follows (I might also add additional depth columns to speed things up), where parents and children are both foreign key relationships to rows from the same entity table.

Entity Relat开发者_开发知识库ionships

composite pkey

child id

parent id

What I am trying to figure out is how to limit the depth of the tree to one. In other words, if somebody is the parent of a child, how to I prevent that parent from being a child in itself, so it is impossible to have grandparents or even further?

---

Depending on your RDBMS, you can handle something like this in an INSERT/UPDATE trigger. For simply restricting a parent to not also be a child it shouldn't be too bad (although I hate using triggers any more than necessary). If you were trying to limit to a certain number of levels (say 100) then you might start to run into performance issues.

In MS SQL Server you can also use user-defined functions in constraints. There are limits however, so I don't know if it would work here. I'll try to test it though.

EDIT:

I just tested this on MS SQL Server 2008 and it looks like it works correctly:

CREATE FUNCTION dbo.Is_Child (@parent_id INT) RETURNS BIT
AS
BEGIN
    DECLARE @return BIT
    IF EXISTS (SELECT * FROM dbo.Test_Trees WHERE child_id = @parent_id)
        SET @return = 1
    ELSE
        SET @return = 0

    RETURN @return
END
GO
CREATE TABLE dbo.Test_Tree_Objects (
    my_id INT NOT NULL,
    CONSTRAINT PK_Test_Tree_Objects PRIMARY KEY CLUSTERED (my_id)
)
CREATE TABLE dbo.Test_Trees (
    my_id INT NOT NULL IDENTITY,
    parent_id INT NOT NULL CHECK (dbo.Is_Child(parent_id) = 0),
    child_id INT NOT NULL,
    CONSTRAINT PK_Test_Trees PRIMARY KEY CLUSTERED (my_id),
    CONSTRAINT FK_Test_Trees_parent_id FOREIGN KEY (parent_id) REFERENCES dbo.Test_Tree_Objects (my_id),
    CONSTRAINT FK_Test_Trees_child_id FOREIGN KEY (child_id) REFERENCES dbo.Test_Tree_Objects (my_id)
)
GO

INSERT INTO dbo.Test_Tree_Objects (my_id) VALUES (1), (2), (3), (4), (5)
GO

INSERT INTO dbo.Test_Trees (parent_id, child_id) VALUES (1, 2)
INSERT INTO dbo.Test_Trees (parent_id, child_id) VALUES (2, 3)

DROP TABLE dbo.Test_Trees
DROP TABLE dbo.Test_Tree_Objects
DROP FUNCTION dbo.Is_Child
GO