self-referencing tables

Does SQL Server 7.0 support "deferred constraints"?  I have self-referencing tables with foreign key constraints between 'child' columns and 'parent' (primary key) columns in the same table.  INSERTing rows using VB and ADO result in run-time error "INSERT statement conflicted with TABLE FOREIGN KEY SAME TABLE constraint <foreign_key_name>...".  I would prefer to not write separate UPDATE statements for the 'child' columns and was wondering what alternatives are available in SQL Server 7.0.

1puttAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sudhi022299Commented:
SQL-Server 7.0 does support having self-referential integrity constraints. post the code of vb which inserts the record.

But unlike in Oracle 8 sql-server doesn't support for deferred constraint checking.

Regards,
Sudhi.
0
Gustavo Perez BuenrostroCommented:
Drop and re-create constraints when you work with 'parents'.
0
Gustavo Perez BuenrostroCommented:
Drop and re-create constraints when you work with 'parents'.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ahoorCommented:
The problem with constraints is that they only support restrict updates. You should use a trigger for cascaded updates:

if update(parent)
   update table
   set      child_column = inserted.parent
   from    child_table, inserted, deleted
   where   child_column = deleted.parent

Notice this only works for single updates (inserted and
deleted both contain 1 row only)

Arjan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
1puttAuthor Commented:
Arjan

Thanks for your insight.

The Parent and Child tables are one and the same.  Will the trigger solution still work?

To clarify my question, the following represents a simplified version of what I am doing.

My INSERT procedure looks something like this:

Create Procedure sp_InsertItem
INSERT INTO Item
  ( ItemCode
   ,ParentItemCode )
VALUES
  (  @ItemCode)
  ( ,@ParentItemCode )

ItemCode is the primary key and is a foreign key to the ParentItemCode.

I am inserting a 'SET' of Items that represent a valid tree structure (it's actually a VB collection object called 'Items').  To save Items in the Items collection to the database, I iterate through the collection calling the sp_InsertItem procedure for each Item in Items.  The Items are inserted in random order; i.e., I do not walk down the tree. If an insert is attempted for an Item, whose ParentItem has not been inserted yet, then the constraint is enforced (as one would anticipate).  

What I currently do (within a transaction) is insert the Items with Item.ParentItemCode = Item.ItemCode and then perform a second iteration where I call an sp_UpdateItemSet procedure wherein I update the ParentItemCode to the 'real' value.

In addition to dropping and adding the Foregin Key constraint, I was wondering what other solutions might be out there to avoid having to execute the update operation.

1Putt

0
ahoorCommented:
Yes it is no problem if the parent and child table are the same,
as long as you consider the parent = inserted and the child = tablename

I would adjust my update to:

if update(parent)
update table
set      child_column = inserted.parent
from    table, inserted, deleted
where   child_column = deleted.parent
and      inserted.parent != deleted.parent
0
ahoorCommented:
Yes it is no problem if the parent and child table are the same,
as long as you consider the parent = inserted and the child = tablename

I would adjust my update to:

if update(parent)
update table
set      child_column = inserted.parent
from    table, inserted, deleted
where   child_column = deleted.parent
and      inserted.parent != deleted.parent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.