Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
R
Constraint Violating Yak Guru
328 Posts |
 Posted - 2006-05-08 : 05:04:37
|
I'm trying to design a table to hold company hierarchy data. I have the following idea that represents departments and sub-departments within a company...tbl_OrgData------------ColumnLevelID int, PKParentLevelID intTitle nvarchar So we can have a department that has a level ID of say, 3.Then we can have 10 sub-departments (4,5,6,7,8...) which all have the ParentLevelID of 3, meaning that in the hierarchy they sit under the parent dept.Within SQL Server 2000, can this rule be defined in the Manage Relationships functionality? I can't seem to be able to create a link back to the same table...Any help greatly appreciated!  |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-05-08 : 05:29:54
|
| Take a look at this article.. if its help shttp://www.sqlteam.com/item.asp?ItemID=8866If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
 |
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2006-05-08 : 06:37:04
|
| HiThat article was helpful. One of the reasons for my approach was the consideration of Cascading Deletes. i.e. if someone deletes the parentID, then all departments under that main department will also be deleted. Is it actually possible to create a relationship from a table back to itself?? |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-05-08 : 07:01:29
|
| Yep. Just code it like you would any Foreign Key constraint. It just so happens that both keys happen to be part of the same table.Mark |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2006-05-08 : 07:18:50
|
Ah it's working now. The dialog box wouldn't work before; it kept switching the table names to something else so I couldn't set the relationship. Perhaps the PK wasn't set or something...  Thank you both for your help  |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-08 : 10:01:57
|
| I don't think you can set a cascading delete when the FK references the same table.CODO ERGO SUM |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-05-08 : 13:34:00
|
| I guess Triggers will be the better option then using Cascade delete.If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
|
|
|
|
|
|
Parent ID and child ID in same table