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?
 
ahoorConnect With a Mentor Commented:
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
 
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Gustavo Perez BuenrostroCommented:
Drop and re-create constraints when you work with 'parents'.
0
 
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
All Courses

From novice to tech pro — start learning today.