| Author |
Topic |
|
BIT
Starting Member
5 Posts |
 Posted - 2008-05-16 : 21:50:42
|
| I have a UniqueIdentifier as a self referencing foreign key. The pk gets set by default using the newid(). I need the foreign key to default to that same value. how can i do that? @@identity doesnt work, $rowguid doesnt work, column name doesnt work. Any ideas? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-17 : 02:29:15
|
| SO you want foreign key to have value of pk automatically? I think you need a trigger for that? But can i ask why are doing this/ |
 |
|
|
BIT
Starting Member
5 Posts |
Posted - 2008-05-17 : 11:12:17
|
| The object represented by my record has a parent/child relationship. So the foreign key forces integrity and its self referencing because they are the same object types. The value is required so the parent record has to list itself as the parent. |
|
|
|
BIT
Starting Member
5 Posts |
Posted - 2008-05-17 : 11:13:40
|
| I actually figured out my fix. I just set it up in the insert sproc (duh). In my defense, i hadnt been to sleep in a while and it was late. :) |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-19 : 02:32:11
|
| Just a general comment; GUIDs should really be avoided if possible as key candidates. They are naturally random in order and they will cause your indexes to be heavily fragmented because the have to be reordered all the time. I'd stay away from them unless it's absolutely necessary that they are *globally* unique.--Lumbago |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-05-19 : 12:44:27
|
| If I am reading your requirement correctly you are setting the ParentID of a record to its own ID? If the record is the root or has no parent shouldn't the ParentID be NULL instead? |
|
|
|
BIT
Starting Member
5 Posts |
Posted - 2008-05-27 : 09:33:20
|
| It cant be null if its a foreign key. Even if that were not true, checking for null vs checking for id=parentid. 6 one way; half dozen another. It must be GUID because of the potential for multiple stand alone deployments to get merged into an enterprise application. Im predicting some scope creep in a few months and im just planning ahead. Thanks for your suggestions. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-05-27 : 13:22:38
|
First, a Foreign key can be NULL. As to performance, I'd have to run some tests. But, I wouldn't dismiss testing for NULL versus comparing columns as equal in performance, especially if the column is indexed.Without knowing your particular environment, I can only guess. But, you might be able to avoid GUIDs by surrogating or by using the ID from the source and combining that with a Application or Source ID column to identify where that rcords came from..?quote:
Originally posted by BIT
It cant be null if its a foreign key. Even if that were not true, checking for null vs checking for id=parentid. 6 one way; half dozen another. It must be GUID because of the potential for multiple stand alone deployments to get merged into an enterprise application. Im predicting some scope creep in a few months and im just planning ahead. Thanks for your suggestions.
|
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-05-27 : 14:58:10
|
| Oh for Pete's sake. Go ahead and use the GUIDs. Freakin' Microsoft even uses GUIDs.e4 d5 xd5 Nf6 |
|
|
|
BIT
Starting Member
5 Posts |
Posted - 2008-05-28 : 19:30:40
|
| I agree with blindman. If it were 10's of millions of records, maybe not. Thanks for you input though. |
|
|
|
|
UniqueIdentifier Self referencing foreign key