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?
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...
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
--
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.
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?
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
--
TheSQLGuru
President
Indicium Resources, Inc.
"ML" <M...@discussions.microsoft.com> wrote in message
news:2B2F2EB8-9B31-4D08...@microsoft.com...
;)
ML
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.
--
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...
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 -:)
Tony,
which books are you speaking about?
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
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...
Thanks Anith and Tony