| Author |
Topic |
|
wbb1975
Starting Member
23 Posts |
 Posted - 2002-07-16 : 09:12:33
|
| Hi to all,I am already wondering for a long time how one could implement a self referencing relationship for one table.Let's assume that I have to add a TreeNode to my database and would add the table TREENODE with the following structureTreeNodeID int NOT NULL - Primary KeyNodeName varchar(50) NOT NULLTopNode int NOT NULLUp to now I have ever used the rule that if there is no TopNode I would have saved 0 for the TopNode and if there is one the TreenodeId of the Top-Node;the checking for this rule would have been done by the business logic, but now I'd like to do this in the database (MSSQL 7);I do not have any problem to build the relationship if there is a top node, but what happens to those records having no top-node?? How can this rule be added to the table with a relationship, or is there a better way???********************UPDATE OF THIS POST:If have just read the article http://www.sqlteam.com/item.asp?ItemID=1353 where there is the other possibility to save the same ID as ParentID if there is not parentID, but this brings me to the same point as mentionend above, how would you place a relation between these two fields in database ...??********************Thanks all your your suggestions, I'm really interested in the answers !!! ;-))Greetings StefanEdited by - wbb1975 on 07/16/2002 09:23:17 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-16 : 09:22:27
|
| Can you give some sample data and the expected rowset? It's not 100% clear (to me) what you are trying to extract from this schema.<O> |
 |
|
|
wbb1975
Starting Member
23 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-16 : 10:22:27
|
| I think this will work:CREATE TABLE Tree (TreeNodeID int NOT NULL Primary Key, NodeName varchar(50) NOT NULL, TopNode int NOT NULL FOREIGN KEY REFERENCES Tree (TreeNodeID) ) |
|
|
|
wbb1975
Starting Member
23 Posts |
Posted - 2002-07-16 : 13:24:52
|
Thanks for your reply!Yes the creation of the table and foreign key does work, but how to input or add records which do not have a topnode???GreetingsStefanquote:
I think this will work:CREATE TABLE Tree (TreeNodeID int NOT NULL Primary Key, NodeName varchar(50) NOT NULL, TopNode int NOT NULL FOREIGN KEY REFERENCES Tree (TreeNodeID) )
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-16 : 13:43:01
|
| You may need to change the table so that TopNode is nullable, that would best solve the problem. Otherwise you'd probably need to create a dead node and use its value as the TopNode. If so, you can always write an UPDATE trigger to change all nodes using that dead value to refer to themselves. If you have SQL 2000 you can write an INSTEAD OF trigger to perform this action too, and circumvent the need to have a dead node. |
|
|
|
wbb1975
Starting Member
23 Posts |
Posted - 2002-07-16 : 13:51:26
|
| OK, but isn't it then better to do like I have ever done - putting these rules into the business logic (if I have a foreign-key-field with null value there could be never rules in the database for valid and invalid inputs) ... that's my question ... isn't there a better way to put this logic into a database ...? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-16 : 14:25:16
|
quote:
OK, but isn't it then better to do like I have ever done - putting these rules into the business logic (if I have a foreign-key-field with null value there could be never rules in the database for valid and invalid inputs) ... that's my question ... isn't there a better way to put this logic into a database ...?
I'm not sure I follow you. I don't recommend relying ONLY on business logic outside of the database layer. Certainly use it, but include logic in the database layer too. That's kinda why you wanted a foreign key in the first place. It is never a bad idea to have more than one layer of data validation.It's perfectly valid to have a Null parent node value in this case, although you'd have to change some logic in your current design if you currently use zero.The thing is, to maintain a foreign key AND use zero as a dead node value, you MUST insert a node with a value of zero. Then you'd have to ensure that that node is never included in any query that you need to execute. You can certainly do it this way, as long as your aware of this issue.If you use NULL however, you can do two things. Re-define your logic to say "A Null parent means that this is a Top Node", or you can write a trigger to change the TopNode value to equal the node's value; in effect, a node that is its own parent. To me, this is a little more logical because I don't have to add a dead node to my table just to insert a new top node.There's no wrong way to accomplish what you're after, as long as you're aware of the side effects of adding the foreign key to your table...and you don't need the foreign key either, it's up to you, but it will definitely help make your data more valid. |
|
|
|
wbb1975
Starting Member
23 Posts |
Posted - 2002-07-17 : 07:46:20
|
quote:
If you use NULL however, you can do two things. Re-define your logic to say "A Null parent means that this is a Top Node", or you can write a trigger to change the TopNode value to equal the node's value; in effect, a node that is its own parent. To me, this is a little more logical because I don't have to add a dead node to my table just to insert a new top node.
Sure I'm with you, I definitely want to put this logic into the database, too. But if I would do as mentioned above, how could I put a foreign key to a null value, that's still my problem ... ???GreetingsStefan |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-17 : 07:54:43
|
| You don't. A foreign key does not (and cannot) reference or enforce a null value. It's only there to enforce the integrity of real values, not nulls. Having a null is fine, you just can't change it to a value that doesn't exist in the table. |
|
|
|
|
self referencing relation within one table