By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,440 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

Constraint question

P: n/a
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?

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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?

Jul 23 '05 #2

P: n/a
(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
Jul 23 '05 #3

P: n/a
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?

Jul 23 '05 #4

P: n/a
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.

Jul 23 '05 #5

P: n/a
(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
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.