self-referencing constraint with identity column?
Dave
a parent-child type of relationship:
ID (identity column)
ParentID (INT column that references the ID column).
To determine if I'm at the top-level of of the relationship, I was going to
leave the ParentID null, otherwise it must be a value of another ID column in
the table which indicates its child of another.
However, can you have it so you don't use NULL to indicate this and instead
set ID and ParentID equal to the same value and still use the IDENTITY column?
So on a INSERT (i.e the IDENTITY would generate 25, so I'd like to set the
ParentID to 25 as well). This would was causing FK violation and I was
wondering if there is a way around it using an insert trigger?
Tom Moreau
would be violated before you could update it through the trigger.
What you might want to do is put in a dummy parent (no social comment here)
with, say, an ID of 0 and a ParentID of 0. For all rows with no parent, you
could use 0. That does not violate your constraint. Then your trigger
could fire and change the 0 to the ID.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dave" <Da...@discussions.microsoft.com> wrote in message
news:F38979FB-3475-429D...@microsoft.com...
David Portas
Avoid using IDENTITY as the target of a self-referencing foreign key.
Either generate the surrogate key independently without an IDENTITY
column, OR keep the IDENTITY column but use a natural key of your
table as the foreign key reference instead.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
--CELKO--
No it is not required at all. Look up the Nested Sets model for trees
and hierarchies. You will find the constraitns are much easier and the
code will run1-2 orders of magnitude faster than recursive path
traversals.
Also, never use IDENTITY in an RDBMS; find a valid relational key
instead.
Alex Kuznetsov
Dave,
What is the business need behind your request? In my experience I
usually need to guarantee that there are no cycles in my hierarchy.
You are trying to store a cycle in yours. Why?
Tony Rogerson
> and hierarchies. You will find the constraitns are much easier and the
> code will run1-2 orders of magnitude faster than recursive path
> traversals.
Rubbish - prove it.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1181656392....@z28g2000prd.googlegroups.com...
ML
ML
TheSQLGuru
EVER seen a SQL Server database that didn't have at least one of those in
it. Guess EVERYONE is designing there databases incorrectly!! :O
--
TheSQLGuru
President
Indicium Resources, Inc.
"ML" <M...@discussions.microsoft.com> wrote in message
news:2B2F2EB8-9B31-4D08...@microsoft.com...
ML
With a tiny correction.
Don't use IDENTITY unless you know how.
;)
ML
Steve Dassin
news:%23U6rM6Q...@TK2MSFTNGP05.phx.gbl...
>> No it is not required at all. Look up the Nested Sets model for trees
>> and hierarchies. You will find the constraitns are much easier and the
>> code will run1-2 orders of magnitude faster than recursive path
>> traversals.
>
> Rubbish - prove it.
How can you prove anything in an area where everything is based on 'it
depends'?
Tony Rogerson
> depends'?
Comparing Nested Sets with Path Traversal?
Its a no brainer, he should very easily be able to give results.
There are two books on my shelf with such benchmarks in and Nested Sets
isn't the winner.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Steve Dassin" <st...@nospamrac4sql.net> wrote in message
news:%235s1B2T...@TK2MSFTNGP02.phx.gbl...
Steve Dassin
>> How can you prove anything in an area where everything is based on 'it
>> depends'?
>
> Comparing Nested Sets with Path Traversal?
>
> Its a no brainer, he should very easily be able to give results.
>
> There are two books on my shelf with such benchmarks in and Nested Sets
> isn't the winner.
Ok, leaving the bird nests aside for a moment what are you wearing around
your neck in your blog pic? I can't quite make it out -:)
Alex Kuznetsov
> > How can you prove anything in an area where everything is based on 'it
> > depends'?
>
> Comparing Nested Sets with Path Traversal?
>
> Its a no brainer, he should very easily be able to give results.
>
> There are two books on my shelf with such benchmarks in and Nested Sets
> isn't the winner.
>
> --
>
> news:%235s1B2T...@TK2MSFTNGP02.phx.gbl...
>
> > "Tony Rogerson" <tonyroger...@torver.net> wrote in message
> >news:%23U6rM6Q...@TK2MSFTNGP05.phx.gbl...
> >>> No it is not required at all. Look up the Nested Sets model for trees
> >>> and hierarchies. You will find the constraitns are much easier and the
> >>> code will run1-2 orders of magnitude faster than recursive path
> >>> traversals.
>
> >> Rubbish - prove it.
>
> > How can you prove anything in an area where everything is based on 'it
> > depends'?
Tony,
which books are you speaking about?
Anith Sen
Not sure which ones Tony speaks of, but Stephane Faroult has a brief
comparison in his book, the Art of SQL. Also, Vadim Tropashko has similar
line in his new "patterns" book too.
--
Anith
Tony Rogerson
Gan; also - Adam Machanic has stuff but I've not fully read that one.
There is another, but it escapes me.
Tony
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Alex Kuznetsov" <AK_TIRE...@hotmail.COM> wrote in message
news:1181740657....@j4g2000prf.googlegroups.com...
Alex Kuznetsov
> I don't have the exact title, but its one of the latest ones from Itzik Ben
> Gan; also - Adam Machanic has stuff but I've not fully read that one.
>
> There is another, but it escapes me.
>
> Tony
>
> --
> [Ramblings from the field from a SQL consultant]http://sqlserverfaq.com
> [UK SQL User Community]
>
Thanks Anith and Tony