Foreign Key self-join on a DataTable

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ev (Ev_at_discussions.microsoft.com)
Date: 03/01/05 

Date: Tue, 1 Mar 2005 11:53:05 -0800

I have a database table in SQL Server that has a self join. In C# I have a
DataTable with a self-join. I have defined a foreign key constraint on the
DataTable for the self join. The AcceptRejectRule is set to Cascade

Table Structure:
OrderID (Identity field)
OrderDesc
OrderParent (this is the self-join - it is a foreign key to OrderID)

In my code I add several rows to the table, createing a hierarchy of orders
e.g.
1000 (OrderParent = NULL
- 1001 (OrderParent = 1000)
- 1002 (OrderParent = 1000)

The OrderID field of the DataTable is set as an AutoIncrement column with
the seed starting at -1. When I insert the records in the database, it
should, in theory, insert Order 1000, get the idenity of the new row, update
the identity in the row, and then update the OrderParent in all child rows.

When I call the DataAdapter Update() method, only the top level rows in the
hierarchy (i.e. Order 1000) are inserted into the table. None of the other
rows are inserted (i.e Order 1001, 1002 aren't in the db). It appears that
the Update() doesn't even try to add those rows. After the Update() method I
checked the rows in the table and they all still have their initial OrderID
and OrderParent values so it doesn't look like the field is being updated
with the idenity value from SQL Server.

If don't set the AcceptRejectRule property, it will attempt to insert the
child rows but since the OrderParent hasn't been updated there is a foreign
key violation in SQL Server.

Any help would be appreciated

Relevant Pages

  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.sqlserver.server)
  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.access.queries)
  • Re: Maximale Zeilenlänge und andere Probleme...
    ... Denn ein Primary Key - sollte niemals aus einem NVARCHAR ... > TypeID NVARCHARNOT NULL, ParentOID UNIQUEIDENTIFIER, FOREIGN ... Der SQL Server erstellt für FOREIGN KEY ...
    (microsoft.public.de.sqlserver)
  • Re: Disable relationships
    ... Thank you, Jacco. ... We have a table that indicates the people in the SQL Server ... >> painful with the current foreign key relationships we have. ...
    (microsoft.public.sqlserver.programming)