Constraint question

[wackyphill]

I'm constructing a menu in a SQL Server database.
Each menu can have sub menus. So my table looks like this:

CREATE TABLE menu
(
id INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(30) NOT NULL,
parentID INT NOT NULL /*ID Of Parent Menu -1 If Root*/
)
IS there a way of placing a constraint on it so if one menu is deleted
all its sub menus get deleted automatically. A normal foreign key
causes a cicrcular problem. Any ideas?

[John Bell]

Hi

I guess you could have a loop in a trigger

WHILE @@ROWCOUNT > 0
BEGIN
DELETE FROM menu
WHERE parentID not in ( SELECT ID FROM menu)
AND ParentID <> 1
END

John

<wa********@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I'm constructing a menu in a SQL Server database.
Each menu can have sub menus. So my table looks like this:

CREATE TABLE menu
(
id INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(30) NOT NULL,
parentID INT NOT NULL /*ID Of Parent Menu -1 If Root*/
)
IS there a way of placing a constraint on it so if one menu is deleted
all its sub menus get deleted automatically. A normal foreign key
causes a cicrcular problem. Any ideas?

[Erland Sommarskog]

(wa********@yahoo.com) writes:

I'm constructing a menu in a SQL Server database.
Each menu can have sub menus. So my table looks like this:

CREATE TABLE menu
(
id INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(30) NOT NULL,
parentID INT NOT NULL /*ID Of Parent Menu -1 If Root*/
)
Better to let parentID be NULL if root. If you go for -1 you cannot
have an fkey constraint anyway.
IS there a way of placing a constraint on it so if one menu is deleted
all its sub menus get deleted automatically. A normal foreign key
causes a cicrcular problem. Any ideas?

You would have to write a trigger, and skip the constraint. Or simply
do the cascading in the stored procedure that removes a menu.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[wackyphill]

Thanks for the info guys.
Better to let parentID be NULL if root. If you go for -1 you cannot
have an fkey constraint anyway. Yeah, good point. Although I was concidering allowing multiple root
menus which is why I did it. Each menu w/ -1 would begin another major
High Level Menu System/Section. And to get a list of all the sections
simply search for the menus w/ a -1 parentID. I thought if things
swelled I'd cut down on the amount of menus that need to be returned in
a query that way. (They will end up being displayed in a tree control
that shows all menu's in the current section).
You would have to write a trigger, and skip the constraint. Or simply
do the cascading in the stored procedure that removes a menu.

OK, I'm just learning SQL Server and didn't want to skip over a feature
that would do it for me if there was one. I'll probably go w/ the
stored procedure method. How best to set it up so a database can only
be accessed through its stored procedures, and stop adhoc SQL commands
that would not inforce the cascading?

[wackyphill]

Although, now that I think about it I certainly could do the same thing
w/ NULLS as w/ -1s :) Sorry, Wasn't thinking that one thru far enough.

[Erland Sommarskog]

(wa********@yahoo.com) writes:

OK, I'm just learning SQL Server and didn't want to skip over a feature
that would do it for me if there was one. I'll probably go w/ the
stored procedure method. How best to set it up so a database can only
be accessed through its stored procedures, and stop adhoc SQL commands
that would not inforce the cascading?

It is of course not possible to lock out ad-hoc statements completely
from Query Analyzer completely for people with admin privileges.
.. But with judicial use of constraints you can prevent bad things from
happening, at least by mistake.

But for application design, yes, it is a good idea make all access with
through stored procedures, and only grant users access to the stored
procedures, but not directly to the tables.

The advantage of doing the cascading in the stored procedure, is that
you can keep a table constraint that prohibits deletion.

Overall, while cascading referential integrity is available in SQL Server,
there are several situations where it is not possible to use it, the
usefulness of the feature is limited.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp