SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 05:14 AM
| ||||
| ||||
 Constraint question 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?      |
|
02-29-2008, 05:14 AM
| |||
| |||
| Re: Constraint question 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 <wackyphill@yahoo.com> wrote in message news:1103232008.918252.175160@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? > |
|
02-29-2008, 05:14 AM
| |||
| |||
| Re: Constraint question (wackyphill@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, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
|
02-29-2008, 05:14 AM
| |||
| |||
| Re: Constraint question > 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? |
Sorry, Wasn't thinking that one thru far enough.|
02-29-2008, 05:16 AM
| ||||
| ||||
| Re: Constraint question (wackyphill@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, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
Linear Mode
